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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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

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

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

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

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.


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.

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


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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Soumik Dutta
Soumik Dutta

Soumik Dutta, having earned a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a key role as an Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Soumik not only skillfully addresses complex challenges but also demonstrates enthusiasm and expertise in gracefully navigating tough situations, underscoring his unwavering commitment to consistently deliver exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo