# How to convert text like 12 kg to a number in Excel

Take the number before the space and scale it by the unit: =VALUE(TEXTBEFORE(A2," "))*IF(TEXTAFTER(A2," ")="g",0.001,1) converts 500 g to 0.5 kg.

**Excel:** `=VALUE(TEXTBEFORE(A2," "))*IF(TEXTAFTER(A2," ")="g",0.001,1)` (Excel 2024 and Microsoft 365)

**Google Sheets:** `=VALUE(LEFT(A2,FIND(" ",A2)-1))*IF(RIGHT(A2,2)="kg",1,0.001)`

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Weight (text) | Kilograms |
| 12 kg |  |
| 500 g |  |
| 1.5 kg |  |
| 750 g |  |
| 20 kg |  |
| 85 g |  |

Formula in B2, filled to B7: `=VALUE(TEXTBEFORE(A2," "))*IF(TEXTAFTER(A2," ")="g",0.001,1)`

Result: 12; 0.5; 1.5; 0.75; 20; 0.085

## How it works

TEXTBEFORE returns the number part ("500") and VALUE makes it a number. TEXTAFTER returns the unit, and the IF multiplies grams by 0.001 so every row ends up in kilograms, ready to sum.

## Other ways

- `=VALUE(LEFT(A2,FIND(" ",A2)-1))*IF(RIGHT(A2,2)="kg",1,0.001)`: Works in every version and in Google Sheets: LEFT up to the space for the number, RIGHT for the unit.

## Common mistakes

- Values without a space, like "12kg", break the split. Insert the space first, or extract the digits with a pattern in Google Sheets.
- Comparing units with = ignores case, so "KG" and "kg" both work, but "kgs" or "lb" need their own case.

## Questions

**How do I convert kilograms to pounds?**

Use CONVERT on the result: `=CONVERT(B2,"kg","lbm")`.

**How do I just remove the unit when all rows use the same one?**

Use `=VALUE(SUBSTITUTE(A2," 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/convert-text-with-units-to-numbers