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

Sometimes, we require to sum only the numbers from a long text string. We can do such a job 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.


Download Practice Workbook

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


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

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.

Excel sum only numbers ignore text in same cell

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.

Windows Calculator in scientific dark mode


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 into the cell.

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

  • Press Enter.

Sum only numbers ignoring text in same cell using SUM, LEFT and FIND Functions

  • 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 the 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 sum all the numbers extracted by the LEFT function and display us the final result. Here, the value is 15,830.

Read More: Sum If a Cell Contains Text in Excel (6 Suitable Formulas)


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 into the cell.

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

  • Then, press Enter.

excel sum only numbers ignore text in same cell using SUMPRODUCT, LEFT and FIND Functions

  • 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 the 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 sum all the numbers extracted by the LEFT function and display us the final result. Here, the value is 15,830.

Read More: How to Use SUMIF with Text in Excel (9 Easy Ways)


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 into the cell.

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

  • Next, press Enter.

Excel sum only numbers ignore text in same cell applying TRIM and IFERROR Functions

  • 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 the 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 us 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 into the cell.

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

  • Press Enter.

Sum only numbers in Excel ignoring text in same cell using SUM, IFERROR and RIGHT Functions

  • 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 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 sum all the numbers get from the IFERROR function and display the final result. Here, the value is 15,830.

Read More: How to Sum If Cell Contains Specific Text in Excel (6 Ways)


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.

Summing only numbers based on categories

The steps of this method are explained below:

πŸ“Œ Steps:

  • In the beginning, select cell F5.
  • Now, write down the following formula into 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.

Summing Only Numbers Based on Categories

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

Product name, price dataset in Excel

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

Showing total of numbers from the cells

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.

Read More: How to Sum Cells with Text and Numbers in Excel (2 Easy Ways)


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’ for opening the Visual Basic Editor.

Opening Visual Basic Editor Tab

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

Using VBA code to sum only numbers in Excel

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 into the cell.

=sum_only_numbers(B5:B14)

  • Press Enter.

Sum only numbers ignoring text in same cell in Excel using Excel VBA

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

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


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.

Don’t forget to check our website, ExcelDemy, for several Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Soumik Dutta

Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo