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.