# How to Sum Cells with Text and Numbers in Excel

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for Excel SUM cells with text and numbers, then you are in the right place. Adding cells in Excel is a simple yet powerful task. There are several ways to sum cells with numeric data in Excel. In this article, we’ll discuss Excel SUM cells with text and numbers.

## 3 Ways to Sum Cells with Text and Numbers in Excel

Excel offers different ways to SUM cells with text and numbers. All of them are very easy to use.

### 1. Using SUBSTITUTE Function to SUM Cells with Text and Numbers

The formula that we used here uses two Excel functions, the SUBSTITUTE function, and the SUM function. Firstly, the formula separates the text from the numeric values using the SUBSTITUTE function, and secondly, adds them up using the SUM function.
Eventually, the syntax of the SUBSTITUTE function is as follows:

`=SUBSTITUTE (text, old_text, new_text, [instance])`

text: The cell or cells to modify.
old_text: The old text to replace.
new_text: The new text to replace with.
instance [Optional]: The instance to replace. By default, all instances will be replaced.
Importantly, the syntax of the SUM function is as follows:

`=SUM(number1, [number2] ,…)`

The SUM function takes 3 types of inputs: positive or negative numeric values, range, and cell references. It takes these inputs and shows their summation as output. The first argument is mandatory, others are optional, and it takes up to 255 numbers.
Here, we need to calculate the Total Weight of the Products in cell D10 by adding the individual weights of Column D. Firstly, write the formula in the D10 cell like this.

`=SUM(SUBSTITUTE(D5:D9, " Kg", "")+0)`

Here, D5:D9 refers to the Weights of Products. Secondly, press ENTER.
Eventually, we’ll get the output as 82. Formula Breakdown:

{=SUM(SUBSTITUTE(D9:D13, ” Kg”, “”)+0)}

• SUM(SUBSTITUTE(“25 Kg, 10 Kg, 10 Kg, 7 Kg, 30, Kg”, ” Kg”, “”)+0)
• SUM(“25, 10, 10, 7, 30” +0)
[Replaced the ” Kg” with empty text.]
• SUM(25, 10, 10, 7, 30)
[After replacing the ” Kg“, Excel still took it as text. By adding the Zero (0), the values were changed to numeric.]
• 82
[The SUM function added the values and returned the desired summation.]

### 2. Utilizing LEFT and FIND Functions

The formula that we used here uses four Excel functions, the FIND function, the LEFT function, the VALUE function, and the SUM function. Firstly, the formula separates the numeric values and secondly adds them up.
Eventually, the syntax of the FIND function is as follows:
This function is used to find a certain text string within a text string.

`=FIND (find_text, within_text, [start_num])`

find_text: The text which is to find.
within_text: The text which is to search within.
start_num [Optional]: The starting position of the search. By default, start_num=1. The first character in within_text is character number 1.
However, the syntax of the LEFT function is as follows:
This function is used to extract characters from text.

`=LEFT (text, [num_chars])`

text: The text string to extract the characters from.
num_chars [Optional]: The number of characters to extract. It starts from the left. By default, num_chars=1.
The syntax of the VALUE function is as follows:
This function converts texts into numeric values.

`=VALUE (text)`

text: The text which is to convert into numbers.
Additionally, you can check out the first method’s the SUM function section for understanding the function. It is explained there.
Here, we also need to calculate the Total Weight of the Products in cell D10 by adding the individual weights of column D. Firstly, write the formula in the D10 cell like this.

`=SUM(VALUE(LEFT(D5:D9,FIND(" ",D5:D9)-1)))` Secondly, press ENTER and get the output as 82. Formula Breakdown:

{=SUM(VALUE(LEFT(D9:D13,FIND(” “,D9:D13)-1)))}

• SUM(VALUE(LEFT(D9:D13,FIND(” “,”25 Kg, 10 Kg, 10 Kg, 7 Kg, 30Kg”)-1)))
• SUM(VALUE(LEFT(D9:D13,(3, 3, 3, 2, 3)-1)))
[The FIND function returned the positions of the White Spaces in each cell.]
• SUM(VALUE(LEFT(D9:D13,(2, 2, 2, 1, 1))))
[Because every number has a White Space at the end, subtracting one from its location gives the digit value of each cell.]
• SUM(VALUE(LEFT(“25 Kg, 10 Kg, 10 Kg, 7 Kg, 30Kg”,(2, 2, 2, 1, 1))))
• SUM(VALUE(25, 10, 10, 7, 30))
[The LEFT function extracted the numeric characters from the text using the digit numbers of each cell.]
• SUM(25, 10, 10, 7, 30)
[The VALUE function converted the numeric characters into numeric values.]
• 82
[The SUM function added the values and returned the desired summation.]

### 3. Applying Combined Formula

We can sum cells with text and numbers using a combination of different functions. We can apply the combination of SUM, IF, ISNUMBER, FIND, and LEFT functions. Suppose we have the dataset below which has column headers as File Number and Size in Columns B and C respectively. The sizes of the files are in 3 different scales KB, MB, and GB. We need to calculate the total of KB, MB, and GB in Column F with the column header of Total. Firstly, write the following formula in the F5 cell like this.

`=SUM(IF(ISNUMBER(FIND(E5,\$C\$5:\$C\$15)),`

`VALUE(LEFT(\$C\$5:\$C\$15,FIND(E5,\$C\$5:\$C\$15)-1)),0)`)

Here, C5:C15 refers to the cells of column header Size and E5 refers to KB in the column header Unit. Secondly, press ENTER.
Thirdly, use the Fill Handle by dragging down the cursor while holding the right-bottom corner of the F5 cell. Eventually, we will get the total of KB, MB, and GB as outputs. ## How to SUM Cells with Text in Excel

We can sum cells with text very easily in Excel. We can use the COUNTIF function in this case to calculate easily. Suppose, we have the following dataset with column headers as Company Name and Branch. We need to find out the sum of common branches in Column F with the column header as Count. Initially, write the following formula in the F5 cell like this.

`=COUNTIF(\$C\$5:\$C\$15,E5)`

Here, C5:C15 refers to the cells with column headers as Branch and E5 refer to USA. Secondly, press ENTER
Lastly, use the Fill Handle to get the output of the Count of different branches. ## Conclusion

That’s all about today’s session. And these are the ways to sum cells with text and numbers in Excel. We strongly believe this article would be highly beneficial for you. Don’t forget to share your thoughts and queries in the comments section and explore our website ExcelDemy, a one-stop Excel solution provider.

## Related Articles #### Chinmoy Mondol

Greetings! Thank you for visiting my profile. I am Chinmoy Mondol. I am a conscientious, tech enthusiast individual with a voracious appetite for knowledge and a desire to learn more. I graduated from American International University-Bangladesh with a Bachelor's Degree in Computer Science and Engineering. I enjoy using my skills to contribute to the exciting technological advances that happen every day. Constant advancement and personal development are my guiding principles.

1. Reply Vaibhav srivastava Apr 11, 2023 at 1:46 PM

in CELL A1 value pass as text 3+4+5+6+7 at Cell A2 sum value which is 25 should be shown.

• Reply Thank you for your very useful suggestion, VAIBHAV SRIVASTAVA. I understand you suggested summing a text string which is much more relatable to this article. And to do so, you can merge the SUM, VALUE, and TEXTSPLIT functions. Here is the combined formula:

`=SUM(VALUE(TEXTSPLIT(A1,"+")))` Don’t hesitate if you have further suggestions for us. Thanks again.

Regards,
Yousuf Khan Shovon Advanced Excel Exercises with Solutions PDF  