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

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)**

**Kg**” with empty text.]

**SUM(25, 10, 10, 7, 30)**

**Kg**“, Excel still took it as text. By adding the

**Zero**(

**0**), the values were changed to numeric.]

**82**

**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**

**Add Rows in Excel with Formula (5 ways)****How to Assign Value to Text and Sum in Excel (2 Easy Methods)****[Fixed!] Excel SUM Formula Is Not Working and Returns 0 (3 Solutions)****Use SUMIF with Text in Excel (9 Easy Ways)****How to Sum Multiple Rows and Columns in Excel**

### 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**.

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)))**

**FIND function**returned the positions of the White Spaces in each cell.]

**SUM(VALUE(LEFT(D9:D13,(2, 2, 2, 1, 1))))**

**SUM(VALUE(LEFT(“25 Kg, 10 Kg, 10 Kg, 7 Kg, 30Kg”,(2, 2, 2, 1, 1))))****SUM(VALUE(25, 10, 10, 7, 30))**

**LEFT function**extracted the numeric characters from the text using the digit numbers of each cell.]

**SUM(25, 10, 10, 7, 30)**

**VALUE function**converted the numeric characters into numeric values.]

**82**

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

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.

**Read More: ****How to Sum Selected Cells in Excel (4 Easy Methods)**

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

**Read More: ****How to Sum Text Values Like Numbers in Excel (3 Methods)**

## 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

**Sum Only Visible Cells in Excel (4 Quick Ways)****How to Sum If Cell Contains Text in Another Cell in Excel****Sum If a Cell Contains Text in Excel (6 Suitable Formulas)****How to Sum Names in Excel (4 Suitable Ways)****Sum Only Positive Numbers in Excel (4 Simple Ways)****How to Sum Only Numbers and Ignore Text in Same Cell in Excel****Sum Values by Day in Excel (6 Methods)**

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, andTEXTSPLITfunctions. 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