Spreadsheet Practice

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

Excel formula

=VALUE(TEXTBEFORE(A2," "))*IF(TEXTAFTER(A2," ")="g",0.001,1)

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.

Result on the example
one result per row in B2, filled down to B7
Works in
Excel 2024 and Microsoft 365
Google Sheets
Different formula, see below

See it work

The formula is already in B2, filled down to B7. Change any input and the result updates, or edit the formula to experiment.

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.

=VALUE(TEXTBEFORE(A2," "))*IF(TEXTAFTER(A2," ")="g",0.001,1)

Result12

  1. A2"12 kg"

    The value in A2.

  2. TEXTBEFORE(A2," ")"12"

    TEXTBEFORE: Text before a delimiter.

    • textA2"12 kg"
    • delimiter" "
  3. VALUE(TEXTBEFORE(A2," "))12

    VALUE: Converts text that looks like a number into a number.

    • textTEXTBEFORE(A2," ")"12"
  4. TEXTAFTER(A2," ")"kg"

    TEXTAFTER: Text after a delimiter.

    • textA2"12 kg"
    • delimiter" "
  5. TEXTAFTER(A2," ")="g"FALSE

    The = operator tests whether the two sides are equal.

  6. IF(TEXTAFTER(A2," ")="g",0.001,1)1

    IF: Returns one value if a test is true, another if false.

    • logical_testTEXTAFTER(A2," ")="g"FALSE
    • value_if_true0.001not used
    • value_if_false1
  7. VALUE(TEXTBEFORE(A2," "))*IF(TEXTAFTER(A2," ")="g",0.001,1)12

    The * operator multiplies.

In Google Sheets

Google Sheets needs a different formula: =VALUE(LEFT(A2,FIND(" ",A2)-1))*IF(RIGHT(A2,2)="kg",1,0.001). On the same data it gives the same result. Here it is running in Google Sheets mode.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=VALUE(LEFT(A2,FIND(" ",A2)-1))*IF(RIGHT(A2,2)="kg",1,0.001)12, 0.5, 1.5…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.

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.

Convert text like 12 kg to a number

In B2, convert the weight to a number of kilograms, dividing grams by 1,000, and fill down to B7.

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","")).

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.