# Why does VALUE return #VALUE! for numbers with units?

VALUE can't read letters. Remove the unit first: =VALUE(SUBSTITUTE(A2," kg","")).

**Excel:** `=VALUE(SUBSTITUTE(A2," kg",""))` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Weight | Number |
| 12.5 kg |  |
| 8 kg |  |
| 0.75 kg |  |
| 1,250 kg |  |
| 40 kg |  |

Formula in B2, filled to B6: `=VALUE(SUBSTITUTE(A2," kg",""))`

Result: 12.5; 8; 0.75; 1250; 40

## How it works

VALUE converts text that looks like a number, but "12.5 kg" has letters in it, so it returns #VALUE!. SUBSTITUTE removes " kg" first, leaving "12.5", which VALUE turns into a real number. The thousands separator in "1,250" is fine.

## Other ways

- `=--SUBSTITUTE(A2," kg","")`: The double minus converts like VALUE.
- `=VALUE(TEXTBEFORE(A2," "))`: Excel 365: keeps whatever comes before the first space, so any unit works.

## Common mistakes

- SUBSTITUTE is case-sensitive: " KG" or " kgs" stay in the text. Nest another SUBSTITUTE, or take the text before the space.
- Text with a comma as the decimal separator ("12,5") depends on your settings. NUMBERVALUE takes the separators as arguments: `=NUMBERVALUE("12,5",",")`.

## Questions

**How do I pull the number out of any text in Google Sheets?**

`=VALUE(REGEXEXTRACT(A2,"[0-9.,]+"))` takes the first run of digits.

**How do I show the unit again after a calculation?**

Join it to the result: `=SUM(B2:B6)&" kg"`.

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/value-function-error-with-units