# How to Sum Cells with Text and Numbers in Excel

### Method 1 – Using the SUBSTITUTE Function to SUM Cells with Text and Numbers

The SUBSTITUTE functionÂ can separate the text from the numeric values.
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.
You can learn about this function in detail by reading this documentation from Microsoft.
Importantly, the syntax of the SUM function is as follows:

`=SUM(number1, [number2] ,â€¦)`

We need to calculate the Total Weight of the Products in cell D10 by adding the individual weights of Column D.

• Insert the following formula in the D10 cell:
`=SUM(SUBSTITUTE(D5:D9, " Kg", "")+0)`

D5:D9 refers to the Weights of Products.

• Press Enter.
• Weâ€™ll get the output.

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.]

Similar Readings

### Method 2 – Utilizing the LEFT and FIND Functions

The syntax of the FIND function is as follows:

`=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.
You can learn about this function in detail by reading this documentation from Microsoft.

The syntax of the LEFT function is as follows:

`=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:

`=VALUE (text)`

text: The text which is to convert into numbers.
You can check out the first method’s the SUM function section for understanding the function. It is explained there.

We also need to calculate the Total Weight of the Products in cell D10 by adding the individual weights of column D.

• Use the following formula in the D10 cell:
`=SUM(VALUE(LEFT(D5:D9,FIND(" ",D5:D9)-1)))`

• Press Enter to get the output.

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.]

### Method 3 – Applying a Combined Formula

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 three different units: KB, MB, and GB. We need to calculate the total of KB, MB, and GB in Column F with the column header of Total.

• Use the following formula in the F5 cell:

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

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

C5:C15 refers to the cells of column header Size and E5 refers to KB in the column header Unit.

• Press Enter.
• Use the Fill Handle by dragging down the cursor while holding the right-bottom corner of the F5 cell.

• We will get the total of KB, MB, and GB as outputs.

## How to SUM Cells with Text via COUNTIF in Excel

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.

• Use the following formula in the F5 cell:
`=COUNTIF(\$C\$5:\$C\$15,E5)`

C5:C15 refers to the cells with column headers as Branch, and E5 refers to USA.

• Press Enter.
• Use the Fill Handle to get the output of the Count of different branches.

Download the Practice Workbook

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Chinmoy Mondol

Chinmoy Mondol is a computer engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in C, C++, Python, JavaScript, HTML, SQL, MySQL, PostgreSQL, Microsoft Office, and VBA and goes beyond the basics. He holds a B.Sc. in Computer Science and Engineering from American International University-Bangladesh. He has transitioned into the role of a content developer. Beyond his work, find him immersed... Read Full Bio

2 Comments
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.

• 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