Split Quantity and Unit in Excel (2 kg, 500 gram) with TEXTBEFORE, TEXTAFTER and NUMBERVALUE: Convert Grams to Kg

By Ravindra Bagale · Last updated: 15 October 2026

Practice file: excel-split-quantity-unit-practice.xlsx: the Grocery sheet with every step in its own column, the older-Excel version below the table, and the no-space example.

Made-up data: the items, quantities and prices are made up for practice, in the style of a quick-commerce order (think Blinkit or Amazon Now).

Which Excel has these functions? TEXTBEFORE and TEXTAFTER: Microsoft 365 and Excel for the web. NUMBERVALUE: Excel 2013 and later. Section 7 shows formulas that work in every version.

1. The raw data

The quantities are text: they sit on the left of the cells. =SUM(B2:B6) returns 0, a favourite interview question. =ISNUMBER(B2) returns FALSE.

The plan for each row:

1. Take the number before the space.
2. Make it a real number.
3. Take the unit after the space.
4. If the unit is gram, divide by 1000 to get kg.
5. Amount = kg × price per kg.

2. TEXTBEFORE: the number part

=TEXTBEFORE(text, delimiter)

The delimiter is the character that separates the parts, here a space. In D2:

=TEXTBEFORE(B2," ")

→ 2. But it is the text "2": it sits on the left, and =ISNUMBER(D2) returns FALSE. TEXTBEFORE always returns text.

3. NUMBERVALUE: text to number

In E2:

=NUMBERVALUE(D2)

→ 2, now a real number on the right; =ISNUMBER(E2) returns TRUE. For "1.5 kg" it gives 1.5, for "500 gram" 500. (=VALUE(D2) does the same for these quantities.)

You can do both steps in one formula:

=NUMBERVALUE(TEXTBEFORE(B2," "))

The rule for nesting: the step that happens first goes inside, the step that happens last goes outside. TEXTBEFORE runs first, so it sits inside NUMBERVALUE.

4. TEXTAFTER: the unit

In F2:

=TEXTAFTER(B2," ")

→ kg. For row 4 it gives gram.

5. Grams to kg, and the amount

The prices are per kg, so every quantity must be in kg. In G2:

=IF(F2="kg",E2,E2/1000)

If the unit is kg, keep the number; otherwise it is grams, so divide by 1000. Then the amount in H2:

=G2*C2

=SUM(H2:H6) → 365, the bill.

6. The no-space trap

TEXTBEFORE and TEXTAFTER look for the delimiter. If a quantity is typed as "2kg" without a space, there is nothing to split at, and both return #N/A.

1. Clean the data first, so that every quantity has one space between the number and the unit. Find & Replace (Ctrl+H) helps when the same mistake repeats, for example replacing kg with kg in cells that have no space yet.
2. Or catch the error: =IFERROR(TEXTBEFORE(B2," "),"no space found") shows a clear message instead of #N/A, so you can fix those rows by hand.

7. Older Excel: LEFT, MID and FIND

Without TEXTBEFORE and TEXTAFTER (Excel 2019, 2021 and older), FIND gives the position of the space and LEFT and MID cut around it:

1. Number: =VALUE(LEFT(B2,FIND(" ",B2)-1)). For "500 gram" the space is the 4th character, so LEFT takes 3 characters: "500".
2. Unit: =MID(B2,FIND(" ",B2)+1,99). MID starts after the space and takes up to 99 characters: "gram".
3. Kg and amount: the same IF and multiplication as above.

The practice file shows these formulas below the table, with the same results.

8. The whole chapter on one page

1. Text quantities cannot be added: SUM returns 0.
2. TEXTBEFORE and TEXTAFTER split at a delimiter and return text.
3. NUMBERVALUE (or VALUE) turns the text into a number.
4. IF converts grams to kg; then multiply by the price per kg.
5. No delimiter means #N/A: clean the data or use IFERROR.

9. Try at home

Recap

Quick revision cards

Englishमराठीहिंदी

Quick revision

Revise this chapter in two minutes. Save the cards on your phone or download them as PNG files. All names and numbers are made up for practice.

More cards for every chapter: Ravi Sir's Cards.