Sometimes, we are required to sum only the numbers from a long text string. We can do such a job by combining our existing functions in several ways. In this article, we will show you six easy methods to sum only numbers and ignore text in the same cell in Excel. If you are also curious about it, download our practice workbook and follow us.

**Table of Contents**Expand

## How to Sum Only Numbers and Ignore Text in Same Cell in Excel: 6 Easy Methods

To demonstrate the approach, we consider a dataset, where we input the 10 employees’ names and their salaries inside the same cell. So, our dataset is in the range of cells **B5:B14**. We will show the sum of the numbers in cell **D5**.

If we calculate the number manually in the calculator, we get the result of the sum is **15,830**. So, after completing every method, the sum will be **15,830**.

### 1. Using SUM, LEFT and FIND Functions

In our first method, we will use the **SUM**, **LEFT**, and **FIND** functions to get the sum of only numbers and ignore text in the same cell. The steps of this method are given below:

**📌 Steps:**

- First of all, select cell
**D5**. - Now, write down the following formula in the cell.

`=SUM(--LEFT(B5:B14,FIND(" ",B5:B14)))`

- Press
**Enter**.

- You will see the sum of the numbers is matched with our manually calculated result.

Thus, we can say that our formula works perfectly, and we are able to sum only numbers and ignore text in the same cell in Excel.

**🔎 Breakdown of the Formula**

We are breaking down the formula for cell **D5**.

`👉`

**FIND(” “,B5:B14)**: The **FIND** function searches for the location of **‘Space’** in all **10** strings. Here, the function returns **5**.

`👉`

**LEFT(B5:B14,FIND(” “,B5:B14))**: The **LEFT** function will extract the values from the strings. The number of characters will be equal to the result of the **FIND** function.

`👉`

**SUM(–LEFT(B5:B14,FIND(” “,B5:B14)))**: Finally, the **SUM** function sums all the numbers extracted by the **LEFT** function and displays us the final result. Here, the value is **15,830**.

### 2. Utilizing SUMPRODUCT, LEFT, and FIND Functions

In this method, we are going to use the **SUMPRODUCT**, **LEFT**, and **FIND** functions to get the sum of only numbers and ignore text in the same cell. The steps of this approach are given as follows:

**📌 Steps:**

- First, select cell
**D5**. - After that, write down the following formula in the cell.

`=SUMPRODUCT(--LEFT(B5:B14,FIND(" ",B5:B14)))`

- Then, press
**Enter**.

- You will get the sum of all the numbers, which is equal to the manual result.

So, we can say that our formula works precisely, and we are able to sum only numbers and ignore text in the same cell in Excel.

**🔎 Breakdown of the Formula**

We are breaking down the formula for cell **D5**.

`👉`

**FIND(” “,B5:B14)**: The **FIND** function searches for the location of **‘Space’** in all **10** strings. Here, the function returns **5**.

`👉`

**LEFT(B5:B14,FIND(” “,B5:B14))**: The **LEFT** function will extract the values from the strings. The number of characters will be equal to the result of the **FIND** function.

`👉`

**SUMPRODUCT(–LEFT(B5:B14,FIND(” “,B5:B14)))**: Finally, the **SUMPRODUCT** function sums all the numbers extracted by the **LEFT** function and displays us the final result. Here, the value is **15,830**.

### 3. Applying TRIM and IFERROR Functions

In this approach, we will use the **SUM**, **TRIM**, **IFERROR**, **LEFT**, and **FIND** functions to get the sum of only numbers and ignore text in the same cell. The steps of this process are given below:

**📌 Steps:**

- At first, select cell
**D5**. - Afterward, write down the following formula in the cell.

`=SUM(--TRIM(IFERROR(LEFT(B5:B14,FIND(" ",B5:B14)),0)))`

- Next, press
**Enter**.

- Finally, you will get the sum of all the numbers, which is equal to the manual result.

Hence, we can say that our formula works effectively, and we are able to sum only numbers and ignore text in the same cell in Excel.

**🔎 Breakdown of the Formula**

We are breaking down the formula for cell **D5**.

`👉`

**FIND(” “,B5:B14)**: The **FIND** function searches for the location of **‘Space’** in all **10** strings. Here, the function returns **5**.

`👉`

**LEFT(B5:B14,FIND(” “,B5:B14))**: The **LEFT** function will extract the values from the strings. The number of characters will be equal to the result of the **FIND** function.

`👉`

**IFERROR(LEFT(B5:B14,FIND(” “,B5:B14)),0)**: The **IFERROR** function checks the result of the **LEFT** function. If there is a value the function will show it, otherwise, the function will show **zero (0)**.

`👉`

**TRIM(IFERROR(LEFT(B5:B14,FIND(” “,B5:B14)),0))**: The **TRIM** function will eliminate all the unnecessary characters from the extracted strings.

`👉`

**SUM(–TRIM(IFERROR(LEFT(B5:B14,FIND(” “,B5:B14)),0)))**: Finally, the **SUM** function sum all the numbers get from the **TRIM** function and display the final result. Here, the value is **15,830**.

### 4. Combining SUM, IFERROR and RIGHT Functions

In this process, the **SUM**, **IFERROR**, and **RIGHT** functions will help us to get the sum of only numbers and ignore text in the same cell. For applying this formula, the numbers must stay after the text. The procedure of this process is explained below step by step:

**📌 Steps:**

- Firstly, select cell
**D5**. - Then, write down the following formula in the cell.

`=SUM(IFERROR(--RIGHT(B5:B14,4),0))`

- Press
**Enter**.

- At last, you will see the sum of all the numbers, which is equal to the manual result.

Therefore, we can say that our formula works successfully, and we are able to sum only numbers and ignore text in the same cell in Excel.

**🔎 Breakdown of the Formula**

We are breaking down the formula for cell **D5**.

`👉`

**RIGHT(B5:B14,4)**: The **RIGHT** function extracts the last **four** digits of the strings. Here, it returns to us the salary of the employees.

`👉`

**IFERROR(–RIGHT(B5:B14,4),0)**: The **IFERROR** function checks the result of the **RIGHT** function. If there is a value the function will show it, otherwise, the function will show **zero** **(0)**.

`👉`

**SUM(IFERROR(–RIGHT(B5:B14,4),0))**: Finally, the **SUM** function sums all the numbers get from the **IFERROR** function and displays the final result. Here, the value is **15,830**.

### 5. Summing Only Numbers Based on Categories

In the following method, we will sum numbers only and ignore the text based on some criteria. To complete the process, we use the **SUM**, **IF**, **ISNUMBER**, **VALUE**, **LEFT**, and **FIND** functions to get the sum of only numbers and ignore text in the same cell. For applying this combined formula, we have to use a different dataset.

The steps of this method are explained below:

**📌 Steps:**

- In the beginning, select cell
**F5**. - Now, write down the following formula in the cell.

`=SUM(IF(ISNUMBER(FIND(E5,$B$5:$C$14)),VALUE(LEFT($C$5:$C$14,FIND(E5,$C$5:$C$14)-1)),0))`

- Then, press
**Enter**.

- After that, double-click on the
**Fill Handle**icon to copy the formula up to cell**F7**.

- Finally, you will see the sum of all the numbers, based on our three desired categories.

At last, we can say that our formula works effectively, and we are able to sum only numbers and ignore text in the same cell in Excel.

**🔎 Breakdown of the Formula**

We are breaking down the formula for cell **F5**.

`👉`

**FIND(E5,$C$5:$C$14)**: The **FIND** function checks the value of the **E5**. Here, the value is **5**.

`👉`

**LEFT($C$5:$C$14,FIND(E5,$C$5:$C$14)-1)**: The **LEFT** function will extract the numbers from the stings. In this cell, the function will return **250**.

`👉`

**VALUE(LEFT($C$5:$C$14,FIND(E5,$C$5:$C$14)-1))**: The **VALUE** function will change the characters from the **Text** to **Value** format.

`👉`

**ISNUMBER(FIND(E5,$B$5:$C$14))**: The **ISNUMBER** function will check the value of the **FIND** function. Here, the function returns **TURE**.

`👉`

**IF(ISNUMBER(FIND(E5,$B$5:$C$14)),VALUE(LEFT($C$5:$C$14,FIND(E5,$C$5:$C$14)-1)),0)**: This function returns **250**.

`👉`

**SUM(IF(ISNUMBER(FIND(E5,$B$5:$C$14)),VALUE(LEFT($C$5:$C$14,FIND(E5,$C$5:$C$14) -1)),0))**: Finally, the **SUM** function sum all the numbers get from the **IF** function and display us the final result. Here, the value is **265**.

### 6. Embedding VBA Code

Writing a VBA code can also help you to sum only numbers and ignore text in the same cell. The steps of this process are given as follows:

**📌 Steps:**

- To start the approach, go to the
**Developer**tab and click on**Visual Basic**. If you don’t have that, you have to enable the Developer tab. Or You can also press**‘Alt+F11’**to open the**Visual Basic Editor**.

- A dialog box will appear.
- Now, in the
**Insert**tab on that box, click the**Module**option.

- Then, write down the following visual code in that empty editor box.

```
Function sum_only_numbers(main_range As Range, Optional str_delim As String = " ") As Double
Dim data As Variant, long_num As Long
For Each elem In main_range
data = Split(elem, str_delim)
For long_num = LBound(data) To UBound(data) Step 1
sum_only_numbers = sum_only_numbers + Val(data(long_num))
Next long_num
Next elem
End Function
```

- Afterward, press
**“Ctrl+S’**to save the code. - Close the
**Editor**tab. - After that, select cell
**D5**and write down the following formula in the cell.

`=sum_only_numbers(B5:B14)`

- Press
**Enter**.

- Thus, you will see the sum of all the numbers, which is equal to the manual result.

Finally, we can say that our visual code worked successfully, and we are able to sum only numbers and ignore text in the same cell in Excel.

**Download Practice Workbook**

Download this practice workbook for practice while you are reading this article.

## Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to sum only numbers and ignore text in the same cell in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.

**Related Articles**

- How to Sum If Cell Contains Text in Another Cell in Excel
- How to Sum Names in Excel
- How to Assign Value to Text and Sum in Excel
- How to Sum Text Values Like Numbers in Excel

**<< Go Back to Excel Sum If Cell Contains Text** **|** **Excel SUMIF Function** **| ****Excel Functions ****|**** Learn Excel**