Adding cells in Excel is a simple yet powerful task. There are several ways to Sum Cells with numeric data in Excel. I have already covered some of those in one of my previous posts. You can check that out here, 4 Ways to Sum Cells in MS Excel. In this article, I have discussed how to sum cells that include both text and numeric data using formulas.

Download the Workbook

You can download the workbook that I used in this article from below and practice with it by yourself.

2 Formulas to Sum Cells with Text and Numbers in MS Excel

1. Using the “SUBSTITUTE” function

The formula that I used here uses two Excel functions, the SUBSTITUTE function, and the SUM function. The formula first separates the text from the numeric values using the SUBSTITUTE function and then adds them up using the SUM function.

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.

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.

You can learn about this function in detail by reading this documentation from Microsoft.

Here, I have calculated the Total Weight of the Products (Cell D14) by adding the individual weights in column D.

The formula is as follows:

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

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 a 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. Using the “LEFT” and “FIND” function

The formula that I used here uses four Excel functions, the FIND function, the LEFT function, the VALUE function, and the SUM function. The formula first separates the numeric values and then adds them up.

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.

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.

You can learn about this function in detail by reading this documentation from Microsoft.

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.

You can learn about this function in detail by reading this documentation from Microsoft.

Check out the first method’s SUM function section for understanding the SUM function. It is explained there.

Here, I have calculated the Total Weight of the Products (Cell D14) by adding the individual weights in column D.

The formula is as follows:

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

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

Conclusion

There are many situations where numbers and texts are associated together in a single cell in Excel. In such scenarios, finding the summations of these kinds of cells becomes a challenging task. This article addressed this issue and provided two Excel formulas that calculate the summations of such cells. I hope you find the solution you were looking for. Please leave a comment if you have any suggestions or questions. Thank you.

The post How to Sum Cells with Text and Numbers in Excel appeared first on ExcelDemy.