How to Sum Only Numbers and Ignore Text in Same Cell in Excel

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.

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.

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

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.

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

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.

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

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.

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

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.

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

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.

• 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