Dear, thank you very much for raising an important question. If empty cells exist in the Weights column, you will get a #VALUE! Error. To handle the #VALUE! Error when empty cells exist, and to sum cells with text and numbers, you can use the following formula: 1. Using SUBSTITUTE Function: =SUM(IFERROR(VALUE(IF(D5:D9"", SUBSTITUTE(D5:D9, " Kg", ""), 0)), 0)) 2. Utilizing LEFT and FIND Functions: =SUM(IFERROR(VALUE(LEFT(D5:D9, FIND(" ",D5:D9) - 1)), 0))
Hello @171sako, Yes, you can. To add the kg in total, use the following formula: =SUM(--SUBSTITUTE(A1:A10,"kg","")) & " kg" Here, I added kg using the ampersand operator to appear with the total sum. Regards ExcelDemy
Hello @mdmamun-lz3qt, To sum cells with text and numbers in Android Excel, you can use formulas similar to desktop Excel, like SUM, SUBSTITUTE, VALUE, or LEFT to extract and sum numbers. However, the mobile interface is limited, so navigating formulas might be more challenging. You can enter the formulas manually, but there's no direct feature to automate this process specifically for mixed text and numbers in Android Excel. Or you can copy-paste the formula. Regards ExcelDemy
Dear @kiranchandwani1390, Thank you for your question. We appreciate your feedback. Regarding your question on how to sum from the middle of texts. Just follow the steps below and check the Excel file linked to this message. In my dataset, the messy data is in column B and the extracted numbers are in column C. The formula is quite large so make sure to expand the formula bar. That said, this formula can extract numbers from any position within a string of text. Finally, use the SUM function to get the sum of all the values. Excel file: www.exceldemy.com/wp-content/uploads/2023/12/Sum-Values-from-Middle-of-Text.xlsx Make sure to stay connected with Exceldemy!❤🥳. Have a good day. Regards, Exceldemy
Hello @ramakrishnag9722, You will need to use an array formula that extracts and sums the numbers from text cells. Here’s a formula-based approach using Excel 365 or Excel 2019 (which support dynamic arrays): =SUM(FILTER(VALUE(TEXTSPLIT(TEXTJOIN(" ",TRUE,A1:A9)," ",TRUE)),ISNUMBER(VALUE(TEXTSPLIT(TEXTJOIN(" ",TRUE,A1:A9)," ",TRUE))))) Explanation of the Formula: TEXTJOIN: Combines all values in the range A1:A9 into a single text string, separated by spaces. TEXTSPLIT: Splits the combined string at each space, creating an array of individual items (both numbers and text). VALUE: Converts text that looks like numbers into actual numbers. ISNUMBER: Checks if each item in the array is a number. FILTER: Filters out only the numeric values from the array. SUM: Adds up all the numeric values from the filtered array. Excel File: www.exceldemy.com/wp-content/uploads/2024/11/SUM-Numeric-Values-from-Text-ExcelDemy.xlsx Regards Shamima Sultana ExcelDemy
Hello @ramakrishnag9722, As you are using Windows 7 you will need to use VBA to sum only the numeric values in a column like this where cells contain both text and numbers. Here's how to do it: Step 1: Use VBA to Create a Custom Function Press Alt + F11 in Excel to open the VBA editor. Go to Insert > Module to create a new module. Copy and paste the following code: Function SumNumbersInText(rng As Range) As Double Dim cell As Range Dim total As Double Dim matches As Object Dim regex As Object Set regex = CreateObject("VBScript.RegExp") regex.Global = True regex.Pattern = "\d+" ' This pattern matches any sequence of digits For Each cell In rng If Not IsEmpty(cell.Value) Then Set matches = regex.Execute(cell.Value) For Each match In matches total = total + CDbl(match.Value) Next match End If Next cell SumNumbersInText = total End Function Close the VBA editor. Step 2: Use the Custom Function in Excel Go back to your Excel sheet. In an empty cell, enter the formula: =SumNumbersInText(A1:A10) Replace A1:A10 with the actual range of your data. Regards ExcelDemy
Thanks For Good Work I Have Following Multiple Entries In Single Cell Of Excel Sheet. I Want Them To Add Up/Sum Up In Single Cell Without Splitting Entries Into Multiple CellS.??? Entry Format Cell "B20" has Rs30, Rs60, Rs 543, rs30, R 453, Rs9000, Rs9999, Rs3218, ANS "C20" Looking For Auto Sum Formula????? PLEASE HELP.
Dear, thank you for your compliment. Your appreciation means a lot to us. To sum up, you can combine the SUM, NUMBERVALUE, SUBSTITUTE, TEXTSPLIT, and LOWER functions in a single cell without splitting entries into multiple cells. Excel Formula: =SUM(NUMBERVALUE(SUBSTITUTE(TEXTSPLIT(LOWER(B20), ","), "rs", ""))) Please check the following: www.exceldemy.com/wp-content/uploads/2024/04/Summing-up-in-a-single-cell-without-splitting-entries-into-multiple-cells.gif You can download the solution workbook: www.exceldemy.com/wp-content/uploads/2024/04/Mushtaq-Ahmed-SOLVED.xlsx
Hello @dwindyloumarinas2268, Sorry to hear it's not working for you! It could be an issue with the cell format or the formula used. Use the SUMPRODUCT or FILTER approach as shown in the tutorial. If your text and numbers are in the same cell, try using =SUMPRODUCT(--TEXTSPLIT(A1,{" ","-","/"})) to extract and sum the numbers. If you'd like, share your formula or a brief example of your data, and I’d be happy to guide you further! 😊 Regards ExcelDemy
@@exceldemy2006 Thank you for the response. My problem is this ( 7:02 - How to SUM Cells with Text in Excel) the sum is wrong. I want to send my excel format to you so i will understand more. 🙏
Hello @dwindyloumarinas2268, You are most welcome. You can share your workbook in our ExcelDemy Forum: exceldemy.com/forum/ We are happy to help. Regards ExcelDemy
Hello @dwindyloumarinas2268, The sum shows 0 because Excel may be treating numbers as text, combining text and numbers in one cell, or due to hidden spaces/special characters. Use this formula to sum numbers in text: =SUMPRODUCT(--TEXTSPLIT(A1,{" ","-","/"})) If numbers are in multiple cells, use: =SUMPRODUCT(--(ISNUMBER(A1:A10)*A1:A10)) Check for spaces or text issues in your data. Regards ExcelDemy
So helpful. Thank you!
very helpful!!
What if there are empty cells in the column?
Dear, thank you very much for raising an important question. If empty cells exist in the Weights column, you will get a #VALUE! Error.
To handle the #VALUE! Error when empty cells exist, and to sum cells with text and numbers, you can use the following formula:
1. Using SUBSTITUTE Function: =SUM(IFERROR(VALUE(IF(D5:D9"", SUBSTITUTE(D5:D9, " Kg", ""), 0)), 0))
2. Utilizing LEFT and FIND Functions: =SUM(IFERROR(VALUE(LEFT(D5:D9, FIND(" ",D5:D9) - 1)), 0))
Can the total also have kg in it
Hello @171sako,
Yes, you can. To add the kg in total, use the following formula:
=SUM(--SUBSTITUTE(A1:A10,"kg","")) & " kg"
Here, I added kg using the ampersand operator to appear with the total sum.
Regards
ExcelDemy
How it sum in android excel ??
Hello @mdmamun-lz3qt,
To sum cells with text and numbers in Android Excel, you can use formulas similar to desktop Excel, like SUM, SUBSTITUTE, VALUE, or LEFT to extract and sum numbers.
However, the mobile interface is limited, so navigating formulas might be more challenging. You can enter the formulas manually, but there's no direct feature to automate this process specifically for mixed text and numbers in Android Excel.
Or you can copy-paste the formula.
Regards
ExcelDemy
How to substrate any number with their units?
Like 100kg -20kg=?
Hello @Trina12332,
Use the following formula:
=LEFT(A1, LEN(A1)-2) - LEFT(B1, LEN(B1)-2) & "kg"
Regards
ExcelDemy
@@exceldemy2006 okk sir thank you so much 🙃🤝
Dear, You are very welcome!
I have messy text and I need to sum of values middle of text
Dear @kiranchandwani1390,
Thank you for your question. We appreciate your feedback. Regarding your question on how to sum from the middle of texts. Just follow the steps below and check the Excel file linked to this message.
In my dataset, the messy data is in column B and the extracted numbers are in column C. The formula is quite large so make sure to expand the formula bar. That said, this formula can extract numbers from any position within a string of text. Finally, use the SUM function to get the sum of all the values.
Excel file: www.exceldemy.com/wp-content/uploads/2023/12/Sum-Values-from-Middle-of-Text.xlsx
Make sure to stay connected with Exceldemy!❤🥳. Have a good day.
Regards,
Exceldemy
P
A
P
L
LC 20
L
LC 30
EG 250
LC 180 & EG 420
This A column data i want sum only numerical numbers
Hello @ramakrishnag9722,
You will need to use an array formula that extracts and sums the numbers from text cells. Here’s a formula-based approach using Excel 365 or Excel 2019 (which support dynamic arrays):
=SUM(FILTER(VALUE(TEXTSPLIT(TEXTJOIN(" ",TRUE,A1:A9)," ",TRUE)),ISNUMBER(VALUE(TEXTSPLIT(TEXTJOIN(" ",TRUE,A1:A9)," ",TRUE)))))
Explanation of the Formula:
TEXTJOIN: Combines all values in the range A1:A9 into a single text string, separated by spaces.
TEXTSPLIT: Splits the combined string at each space, creating an array of individual items (both numbers and text).
VALUE: Converts text that looks like numbers into actual numbers.
ISNUMBER: Checks if each item in the array is a number.
FILTER: Filters out only the numeric values from the array.
SUM: Adds up all the numeric values from the filtered array.
Excel File:
www.exceldemy.com/wp-content/uploads/2024/11/SUM-Numeric-Values-from-Text-ExcelDemy.xlsx
Regards
Shamima Sultana
ExcelDemy
Windows 7 formula not working
Hello @ramakrishnag9722,
As you are using Windows 7 you will need to use VBA to sum only the numeric values in a column like this where cells contain both text and numbers.
Here's how to do it:
Step 1: Use VBA to Create a Custom Function
Press Alt + F11 in Excel to open the VBA editor.
Go to Insert > Module to create a new module.
Copy and paste the following code:
Function SumNumbersInText(rng As Range) As Double
Dim cell As Range
Dim total As Double
Dim matches As Object
Dim regex As Object
Set regex = CreateObject("VBScript.RegExp")
regex.Global = True
regex.Pattern = "\d+" ' This pattern matches any sequence of digits
For Each cell In rng
If Not IsEmpty(cell.Value) Then
Set matches = regex.Execute(cell.Value)
For Each match In matches
total = total + CDbl(match.Value)
Next match
End If
Next cell
SumNumbersInText = total
End Function
Close the VBA editor.
Step 2: Use the Custom Function in Excel
Go back to your Excel sheet.
In an empty cell, enter the formula:
=SumNumbersInText(A1:A10)
Replace A1:A10 with the actual range of your data.
Regards
ExcelDemy
Thanks For Good Work
I Have Following Multiple Entries In Single Cell Of Excel Sheet. I Want Them To Add Up/Sum Up In Single Cell Without Splitting Entries Into Multiple CellS.???
Entry Format
Cell "B20" has Rs30, Rs60, Rs 543, rs30, R 453, Rs9000, Rs9999, Rs3218, ANS "C20"
Looking For Auto Sum Formula?????
PLEASE HELP.
Dear, thank you for your compliment. Your appreciation means a lot to us. To sum up, you can combine the SUM, NUMBERVALUE, SUBSTITUTE, TEXTSPLIT, and LOWER functions in a single cell without splitting entries into multiple cells.
Excel Formula: =SUM(NUMBERVALUE(SUBSTITUTE(TEXTSPLIT(LOWER(B20), ","), "rs", "")))
Please check the following: www.exceldemy.com/wp-content/uploads/2024/04/Summing-up-in-a-single-cell-without-splitting-entries-into-multiple-cells.gif
You can download the solution workbook: www.exceldemy.com/wp-content/uploads/2024/04/Mushtaq-Ahmed-SOLVED.xlsx
Its not working for me. I swear!
Hello @dwindyloumarinas2268,
Sorry to hear it's not working for you! It could be an issue with the cell format or the formula used. Use the SUMPRODUCT or FILTER approach as shown in the tutorial. If your text and numbers are in the same cell, try using =SUMPRODUCT(--TEXTSPLIT(A1,{" ","-","/"})) to extract and sum the numbers.
If you'd like, share your formula or a brief example of your data, and I’d be happy to guide you further! 😊
Regards
ExcelDemy
@@exceldemy2006 Thank you for the response. My problem is this ( 7:02 - How to SUM Cells with Text in Excel) the sum is wrong.
I want to send my excel format to you so i will understand more.
🙏
Hello @dwindyloumarinas2268,
You are most welcome. You can share your workbook in our ExcelDemy Forum: exceldemy.com/forum/
We are happy to help.
Regards
ExcelDemy
the sum of me is 0
Hello @dwindyloumarinas2268,
The sum shows 0 because Excel may be treating numbers as text, combining text and numbers in one cell, or due to hidden spaces/special characters. Use this formula to sum numbers in text:
=SUMPRODUCT(--TEXTSPLIT(A1,{" ","-","/"}))
If numbers are in multiple cells, use:
=SUMPRODUCT(--(ISNUMBER(A1:A10)*A1:A10))
Check for spaces or text issues in your data.
Regards
ExcelDemy