How to Sum Cells with Text and Numbers in Excel

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.


Download Practice Workbook


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.
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] ,…)

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.

excel sum cells with text and numbers

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.

Using SUBSTITUTE Function

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

Read More: All the Easy Ways to Add up (Sum) a column in Excel

Similar Readings


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

excel sum cells with text and numbers

Firstly, write the formula in the D10 cell like this.

=SUM(VALUE(LEFT(D5:D9,FIND(" ",D5:D9)-1)))

Utilizing LEFT and FIND Functions

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

Read More: How to Add Numbers in Excel (2 Easy Ways)


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.

excel sum cells with text and numbers

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.

Applying a Combination of Functions

Secondly, press ENTER.
Thirdly, use the Fill Handle by dragging down the cursor while holding the right-bottom corner of the F5 cell.

excel sum cells with text and numbers

Eventually, we will get the total of KB, MB, and GB as outputs.

excel sum cells with text and numbers


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.

How to SUM Cells with Text in Excel

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.

excel sum cells with text and numbers

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.


Further Readings

Chinmoy Mondol

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo