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