# How to sum cells that contain numbers and text in Excel

Strip the text and convert what is left: `=SUMPRODUCT(--SUBSTITUTE(B2:B6," kg",""))` adds "12 kg", "8 kg" and so on.

**Excel:** `=SUMPRODUCT(--SUBSTITUTE(B2:B6," kg",""))` (Microsoft 365 only)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Item | Weight |  | Total kg |
| Flour | 12 kg |  |  |
| Sugar | 8 kg |  |  |
| Rice | 25 kg |  |  |
| Oats | 5 kg |  |  |
| Lentils | 3 kg |  |  |

Formula in E1: `=SUMPRODUCT(--SUBSTITUTE(B2:B6," kg",""))`

Result: 53

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

## Other ways

- `=SUM(--TEXTBEFORE(B2:B6," "))`: Excel 365: keeps everything before the first space, whatever the unit is.
- `=SUMPRODUCT(--REGEXEXTRACT(B2:B6,"[0-9.]+"))`: 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.

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

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/sum-cells-that-contain-numbers-and-text