How to sum cells that contain numbers and text in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(--SUBSTITUTE(B2:B6," kg",""))Strip the text and convert what is left: =SUMPRODUCT(--SUBSTITUTE(B2:B6," kg","")) adds "12 kg", "8 kg" and so on.
- Result on the example
- 53 in
E1 - Works in
- Microsoft 365 only
- Google Sheets
- Same formula
See it work
The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.
How it works
"12 kg" is text, so SUM skips it. SUBSTITUTE removes " kg" from every cell, leaving "12", "8" and so on, still as text. The double minus turns them into numbers, and SUMPRODUCT adds the array without Ctrl+Shift+Enter.
=SUMPRODUCT(--SUBSTITUTE(B2:B6," kg",""))Result53
B2:B65 rows × 1 col: "12 kg", "8 kg", "25 kg", "5 kg", …A range: 5 cells in one column.
SUBSTITUTE(B2:B6," kg",""){5 rows × 1 col} "12", "8", "25", "5", …SUBSTITUTE: Replaces matching text.- text
B2:B65 rows × 1 col: "12 kg", "8 kg", "25 kg", "5 kg", … - old_text
" kg" - new_text
""
- text
-SUBSTITUTE(B2:B6," kg",""){5 rows × 1 col} -12, -8, -25, -5, …Makes the value negative.
--SUBSTITUTE(B2:B6," kg",""){5 rows × 1 col} 12, 8, 25, 5, …Makes the value negative.
SUMPRODUCT(--SUBSTITUTE(B2:B6," kg",""))53SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--SUBSTITUTE(B2:B6," kg",""){5 rows × 1 col} 12, 8, 25, 5, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(--SUBSTITUTE(B2:B6," kg","")). We ran it in Google Sheets mode on the same data and it returns 53.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUM(--TEXTBEFORE(B2:B6," "))Excel only | 53 | Excel 365: keeps everything before the first space, whatever the unit is. |
| =SUMPRODUCT(--REGEXEXTRACT(B2:B6,"[0-9.]+"))Google Sheets | 53 | Google Sheets: pulls the first number out of each cell, whatever text surrounds it. |
Common mistakes
- If one cell has a different unit or spacing ("12kg", "8 KG"), SUBSTITUTE leaves text behind and the formula returns #VALUE!. SUBSTITUTE is case-sensitive.
- Better long term: keep the number in its own column and the unit in the header ("Weight (kg)"), so plain SUM works.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Sum cells that contain numbers and text
E1, add up the weights in B2:B6, which are stored as text like "12 kg".Questions
How do I sum numbers with a currency sign stored as text?
Remove the sign the same way: =SUMPRODUCT(--SUBSTITUTE(B2:B6,"$","")).
How do I extract the number into its own column?
Use =--SUBSTITUTE(B2," kg","") filled down, or Data > Text to Columns split on the space.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.