Excel Split Numbers From Units of Measure

This guide shows you how to split numbers from units of measure in Excel, such as turning “10 kg” into 10 and “kg” in separate columns. You can use Flash Fill, Text to Columns, or formulas depending on whether the units are consistent, at the end, or mixed with spaces. The method you choose depends on your data’s structure and whether you need the result to update automatically.

Formula

The formula above extracts the numeric part and the unit part from a text string. It works by finding the first space or the first occurrence of a known unit abbreviation, then splitting at that position. The variables are:

• t: the original text cell, e.g., “10 kg”.
• num: the numeric portion, converted to a real number with VALUE().
• unit: the remaining text after the number, trimmed of extra spaces.

The core expression uses FIND() to locate the earliest position of any delimiter (space or unit) in the string. The array constant {” “,”kg”,”lb”,”cm”,”m”,”g”} lists possible delimiters, and appending them to the text ensures FIND never returns an error if a unit is missing. MIN() picks the smallest position, which is the first split point.

Worked Example

Take the default value “10 kg”.

1. Append the delimiters: “10 kg kg lb cm m g”.
2. FIND each delimiter in that string: space is at 3, “kg” is at 4, “lb” is at 10, “cm” at 13, “m” at 16, “g” at 18.
3. MIN of those positions is 3 (the space).
4. LEFT(“10 kg”, 3-1) = “10”. VALUE(“10”) = 10.
5. MID(“10 kg”, 3, LEN(“10 kg”)) = ” kg”. TRIM(” kg”) = “kg”.

The result is 10 and “kg”, matching the calculator output.

Why You Need to Split Numbers and Units

Excel treats text as text, so “10 kg” cannot be summed, averaged, or used in arithmetic. If you import data from a scale, a web page, or a legacy system, you often get combined values like “5ft 3in” or “12.5 ml”. Splitting them lets you convert units, sort by magnitude, or create charts. Without splitting, you would need to manually retype every value, which is error-prone and slow.

This is a common task in inventory management, recipe scaling, shipping logs, and scientific data cleaning. The same principle applies to currency symbols (“$1,299”), percentages (“45%”), or time suffixes (“2h 30m”).

Method 1: Flash Fill (Easiest for One-Time Cleanup)

Flash Fill is the quickest way when your data has a consistent pattern and you do not need formulas. It works by recognizing the pattern from your manual input.

1. In the column next to your data, type the number from the first cell manually (e.g., type 10 for “10 kg”).
2. Press Enter, then start typing the number from the second cell.
3. Excel will suggest a fill for the rest of the column. Press Enter to accept.
4. Repeat for the unit column, typing “kg” for the first row.

Flash Fill works best when the pattern is obvious: all numbers at the start, all units at the end, no exceptions. If you have mixed formats like “10kg” and “10 kg”, Flash Fill may fail or produce inconsistent results. It also does not update if your source data changes, so use it only for static data.

Method 2: Text to Columns (Good for Fixed Delimiters)

Text to Columns is a built-in tool that splits text based on a delimiter, such as a space, comma, or tab. It is ideal when your data always has a space between the number and the unit.

1. Select the column with your combined data.
2. Go to the Data tab and click “Text to Columns”.
3. Choose “Delimited” and click Next.
4. Check “Space” as the delimiter (uncheck others). Click Next.
5. Choose a destination cell (e.g., the cell next to your data) and click Finish.

This splits “10 kg” into two columns: “10” and “kg”. The number column may be stored as text, so you may need to convert it to a number using the “Text to Columns” final step (choose “General” for the column data format) or by multiplying by 1. Text to Columns is static, so it does not update if the source changes.

Method 3: Formulas (For Dynamic Updating)

If your data changes frequently or you need a live result, use formulas. The approach depends on whether the number is always at the beginning, always at the end, or somewhere in between.

• Number at the start, unit at the end with a space: Use the formula in the calculator block, or simpler: =LEFT(A1, FIND(" ", A1)-1) for the number and =RIGHT(A1, LEN(A1)-FIND(" ", A1)) for the unit.
• Number at the end, unit at the start: Use =RIGHT(A1, LEN(A1)-FIND(" ", A1)) for the number and =LEFT(A1, FIND(" ", A1)-1) for the unit.
• No space (e.g., “10kg”): Use the array formula from the calculator block, or use a helper column with =MIN(FIND({"kg","lb","cm","m","g"}, A1 & "kg lb cm m g")) to find the unit position.

Formulas are powerful but require careful handling of errors. If a cell does not contain a recognized unit, the FIND function returns an error. The calculator block’s formula avoids this by appending all possible units to the text, but you must update the list to match your actual units.

Common Mistakes and How to Avoid Them

The most frequent error is assuming the space is always present. Data like “10kg” or “10 kg” mixed together will break simple LEFT/RIGHT formulas. Another mistake is forgetting that FIND is case-sensitive: “KG” will not match “kg”. Use SEARCH instead if you need case-insensitive matching.

Rounding is another issue. When you extract a number like “12.50”, Excel may show it as 12.5, losing the trailing zero. If you need to keep the original formatting, store the number as text or use a custom number format. Also, beware of leading/trailing spaces: always wrap extracted text in TRIM() to avoid invisible characters that break VLOOKUP or IF statements.

Finally, do not use the VALUE() function on text that includes a unit, like “10 kg” – it will return a #VALUE! error. Always split first, then convert.

When to Use Each Method

• Flash Fill: One-time cleanup, small dataset, consistent pattern, no need for updates.
• Text to Columns: Medium-sized dataset, consistent delimiter (space or comma), static data, quick and easy.
• Formulas: Large dataset, dynamic data, need for automatic updates, mixed formats, or when you want to keep the original column intact.

For most users, Flash Fill is the fastest for a quick fix, but formulas are the only way to build a reusable template. If you are working with a database that updates daily, invest time in formulas to avoid redoing the split every time.

Alternatives to Splitting Manually

If you are using Excel 365, you can use the TEXTSPLIT function, which splits text by a delimiter into separate cells or rows. For example, =TEXTSPLIT(A1, " ") splits “10 kg” into two cells. This is simpler than the older formulas but still requires the delimiter to be consistent.

For data with irregular patterns, Power Query (Get & Transform) is a robust alternative. You can load your data, split columns by delimiter or by position, and refresh the query when the source changes. Power Query handles large datasets well and does not require writing complex formulas.

If you only need the numeric value for calculations, you can also use the VALUE function in combination with SUBSTITUTE to remove known units: =VALUE(SUBSTITUTE(A1, "kg", "")). This works only when the unit is a fixed string at the end and there are no spaces.

Frequently Asked Questions

How do I split “10kg” without a space?

Use the array formula from the calculator block, or use Power Query’s “Split by Character Transition” option, which splits when the character changes from digit to letter. In Power Query, go to Transform > Split Column > By Character Transition and choose “Digit to Non-Digit”.

Why does my extracted number show as text and not a number?

Because LEFT() returns text. Multiply the result by 1 or use VALUE() to convert it. You can also use the “Text to Columns” tool and choose “General” for the column format, which converts text to numbers automatically.

Can I split numbers from units in Google Sheets the same way?

Yes, most formulas work in Google Sheets. Flash Fill is not available, but you can use the SPLIT function: =SPLIT(A1, " ") for space-delimited data. For no-space data, use REGEXEXTRACT: =REGEXEXTRACT(A1, "[0-9.]+") for the number and =REGEXEXTRACT(A1, "[a-zA-Z]+") for the unit.

What if my unit has a prefix like “mm” or “kg”?

The formula in the calculator block lists each unit explicitly. If you have many prefixes, add them to the list. For a general solution, use a regex to find the first letter after the number: =MID(A1, MIN(FIND({"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z"}, A1 & "abcdefghijklmnopqrstuvwxyz")), LEN(A1)). This finds the first alphabetic character, which works for any unit.

How do I split multiple numbers like “5ft 3in” into separate columns?

Use Text to Columns with a space delimiter, then repeat the process on each resulting column. Or use a formula with nested FIND to locate the second space. For a robust solution, use Power Query to split by each space and then split again by the unit boundary.

Is there a way to split numbers and units without losing the original data?

Yes, always use a separate column for the split results. Flash Fill and Text to Columns can overwrite the original if you choose the wrong destination. Formulas never modify the original cell, so they are the safest option.

Related

• UPI vs Cards: Merchant Fees & Customer Choice
• VLC Record Screen With Audio: Full Guide
• Data Centers vs Cloud: Environmental Footprint

in t | Text with number and unit | - | 10 kg | 0
out num | Number | - | VALUE(LEFT(t, MIN(FIND({" ","kg","lb","cm","m","g"}, t & " kg lb cm m g")-1))) | 2
out unit | Unit | - | TRIM(MID(t, MIN(FIND({" ","kg","lb","cm","m","g"}, t & " kg lb cm m g")), LEN(t))) | 0