How to Use the MID Function to Sum the Digits of a Number in Excel

The MID function in Excel is used to extract a substring from a text string, given a starting position and a length. For example, =MID("Hello",2,3) returns ell, which is the substring of “Hello” starting from the second character and having a length of three characters.

The MID function can also be used to extract the digits of a number, by converting the number to a text string first. For example, =MID(TEXT(1234,"0"),2,2) returns 23, which is the substring of “1234” starting from the second character and having a length of two characters.

To sum the digits of a number, we can use the MID function in combination with other functions, such as SUM, LEN, and ROW. In this article, we will explain the basic theory and the procedures of using the MID function to sum the digits of a number in Excel. We will also provide a comprehensive explanation with a detailed example and an Excel table. Finally, we will discuss some other approaches to achieve the same result.

The basic idea of using the MID function to sum the digits of a number is to extract each digit of the number as a separate text string, and then convert it back to a number and add them up. For example, to sum the digits of 1234, we can do the following steps:

• Convert 1234 to a text string using the TEXT function: =TEXT(1234,"0")
• Extract the first digit using the MID function: =MID(TEXT(1234,"0"),1,1)
• Convert the first digit back to a number using the VALUE function: =VALUE(MID(TEXT(1234,"0"),1,1))
• Repeat the same process for the second, third, and fourth digits, and add them up using the SUM function: =SUM(VALUE(MID(TEXT(1234,"0"),1,1)),VALUE(MID(TEXT(1234,"0"),2,1)),VALUE(MID(TEXT(1234,"0"),3,1)),VALUE(MID(TEXT(1234,"0"),4,1)))
• The result is 10, which is the sum of the digits of 1234.

However, this method is tedious and impractical, especially if the number has many digits. A better way is to use an array formula, which can perform multiple calculations in one cell. An array formula is entered by pressing Ctrl+Shift+Enter, instead of just Enter. The formula will be surrounded by curly braces { } to indicate that it is an array formula.

To use an array formula with the MID function, we need to use the ROW function to generate an array of numbers that represent the positions of each digit in the number. For example, to generate an array of numbers from 1 to 4, we can use =ROW(1:4). This will return {1;2;3;4} as an array.

We can then use the ROW function as the second argument of the MID function, to extract each digit of the number as a separate text string. For example, =MID(TEXT(1234,"0"),ROW(1:4),1) will return {“1”;“2”;“3”;“4”} as an array.

We can then use the VALUE function to convert each text string to a number, and use the SUM function to add them up. For example, =SUM(VALUE(MID(TEXT(1234,"0"),ROW(1:4),1))) will return 10 as the result.

To make the formula more flexible, we can use the LEN function to get the length of the number, and use it as the argument of the ROW function. For example, =ROW(1:LEN(1234)) will return {1;2;3;4} as an array, and =ROW(1:LEN(56789)) will return {1;2;3;4;5} as an array.

Therefore, the general formula to use the MID function to sum the digits of a number in Excel is:

=SUM(VALUE(MID(TEXT(number,"0"),ROW(1:LEN(number)),1)))

This is an array formula, so remember to press Ctrl+Shift+Enter after entering it.

Procedures

To use the MID function to sum the digits of a number in Excel, follow these steps:

• Enter the number in a cell, such as A1.
• In another cell, such as B1, enter the formula =SUM(VALUE(MID(TEXT(A1,"0"),ROW(1:LEN(A1)),1)))
• Press Ctrl+Shift+Enter to enter the formula as an array formula. The formula will be surrounded by curly braces { }.
• The result will be displayed in the cell, such as B1.

Explanation

To illustrate the use of the MID function to sum the digits of a number in Excel, let us consider a scenario where we want to calculate the sum of the digits of the product of two numbers. For example, suppose we have two numbers, 12 and 34, in cells A2 and B2, respectively. We want to calculate the product of these two numbers, and then sum the digits of the product.

To do this, we can use the following steps:

• In cell C2, enter the formula =A2*B2 to calculate the product of 12 and 34. The result is 408.
• In cell D2, enter the formula =SUM(VALUE(MID(TEXT(C2,"0"),ROW(1:LEN(C2)),1))) to sum the digits of 408. The result is 12.

We can use an Excel table to show the intermediate steps of the calculation, as shown below:

The formula in cell D2 can be explained as follows:

• The TEXT function converts the number 408 to a text string “408”.
• The LEN function returns the length of the text string “408”, which is 3.
• The ROW function generates an array of numbers from 1 to 3, which are {1;2;3}.
• The MID function extracts each digit of the text string “408” as a separate text string, using the array of numbers as the starting positions and 1 as the length. The result is an array of text strings {“4”;“0”;“8”}.
• The VALUE function converts each text string to a number, resulting in an array of numbers {4;0;8}.
• The SUM function adds up the numbers in the array, resulting in 12.

Other Approaches

There are some other ways to sum the digits of a number in Excel, without using the MID function. Here are some examples:

• Using the MOD and INT functions: The MOD function returns the remainder of a division, and the INT function returns the integer part of a number. We can use these functions to extract each digit of a number by dividing the number by 10 repeatedly, and adding up the remainders. For example, to sum the digits of 408, we can use the formula =MOD(408,10)+MOD(INT(408/10),10)+MOD(INT(408/100),10). The result is 12.
• Using the RIGHT and LEFT functions: The RIGHT function returns the rightmost characters of a text string, and the LEFT function returns the leftmost characters of a text string. We can use these functions to extract each digit of a number by converting the number to a text string, and using the length of the text string as the argument. For example, to sum the digits of 408, we can use the formula =VALUE(RIGHT(TEXT(408,"0"),1))+VALUE(RIGHT(TEXT(408,"0"),2))-VALUE(LEFT(TEXT(408,"0"),1))+VALUE(LEFT(TEXT(408,"0"),1)). The result is 12.