How to Split Numbers From Units Of Measure in Excel

Why This Task Matters in Excel

Every day, analysts, engineers, buyers, and project managers receive data where quantities and their units of measure sit together in the same cell—“250 kg”, “12.5 m”, “3 L”, “6 ft 2 in”, or “48 pcs”. While that looks fine to the human eye, it seriously limits what you can do in Excel. A value that reads “250 kg” cannot participate in numeric aggregations, charts, or conditional formatting because Excel sees the entire string as text. Likewise, having the unit of measure trapped next to the number prevents automated conversions, validation, or dynamic reporting.

Picture a supply-chain dashboard. Procurement receives hundreds of lines from different vendors, each entering their weights differently—some write “0.75 lb”, others “0,75 kg”, and still more “750 g”. To compare apples to apples, you first have to detach the numeric portion, convert it to a consistent unit, and then summarise. The same goes for scientific experiments where readings such as “5.2 mL” must be converted to “5 200 µL”, or construction estimates where lengths like “12 ft 6 in” have to be split before converting everything to millimetres.

Excel is ideally suited for this clean-up because you can build a repeatable formula that automatically handles new rows of data. Once you know how to split the number from the unit, you unlock the entire Excel toolset: you can feed the numbers into SUMIFS, forecast with TREND, or visualise them with charts. Fail to do the split and you face manual editing, hidden errors, and inconsistent results that jeopardise budgets, lab results, or regulatory reporting. Mastering this task therefore pays dividends across finance, manufacturing, research, and logistics, and it dovetails neatly with data-cleaning skills such as text-to-columns, data validation, and dynamic arrays.

Best Excel Approach

The most reliable, future-proof way to split numbers from units in modern Excel (Microsoft 365 or Excel 2021) is to combine the powerful REGEX.EXTRACT function with LET and TRIM. Regular expressions precisely target the numeric portion—even if it contains decimals, commas, or minus signs—leaving everything else as the unit. Because REGEX.EXTRACT is case-insensitive and supports pattern matching, it copes with “250kg”, “250 kg”, or “ 250 kg ” equally well.

When to choose this approach

• Use it when you have Microsoft 365 or Excel 2021+ where REGEX.* functions are available.
• Ideal when the number can appear at the front, middle, or end of the string, or when there may be optional spaces or symbols.
• Prefer it over Text-to-Columns because it remains dynamic—new entries are handled automatically.

Logic behind the solution

1. Identify the numeric portion with a pattern that matches digits plus an optional decimal separator.
2. Convert that extracted text to a real number with VALUE.
3. Remove the numeric portion from the original string, trim the remainder, and you have the unit.
4. Spill both results side by side so one formula services entire columns.

Syntax (recommended dynamic spill formula):

=LET(
    txt, A2,
    numText, REGEX.EXTRACT(txt,"[0-9]+([.,][0-9]+)?"),
    number, VALUE(SUBSTITUTE(numText,",",".")),
    unit, TRIM(SUBSTITUTE(txt,numText,"")),
    CHOOSE({1,2}, number, unit)
)

Alternative for Excel 2019 and earlier (no RegEx):

=LET(
    txt, A2,
    splitPos, MATCH(TRUE,ISNUMBER(--MID(txt,SEQUENCE(LEN(txt)),1)),0),
    number, --LEFT(txt,splitPos+MATCH(FALSE,ISNUMBER(--MID(txt,SEQUENCE(LEN(txt)-splitPos+1, splitPos),1)),0)-2),
    unit, TRIM(REPLACE(txt,1,LEN(number),"")),
    CHOOSE({1,2}, number, unit)
)

Parameters and Inputs

• Input cell (txt) – Any cell containing a quantity plus its unit, e.g., “12.5 m” or “300pcs”.
• Pattern [0-9]+([.,][0-9]+)? – Matches one or more digits followed optionally by a decimal comma or dot and further digits. Modify if you expect fractions or plus/minus signs.
• Decimal separator – The formula converts commas to dots before VALUE, ensuring European-style decimals work.
• Optional spaces – TRIM removes surplus spaces so “ 15 km ” becomes a clean “km”.
• Validation – Non-numeric strings will return an error. Wrap the final formula in IFERROR when blank cells or headers may appear.
• Edge cases – Multiple numbers in one cell? The pattern grabs only the first. Use a different pattern or REGEX.EXTRACT(txt,"[0-9.]+$") to capture the last one.
• Data preparation – No requirement to pre-clean; however, consistent use of either dot or comma as decimal separator simplifies conversion.

Step-by-Step Examples

Example 1: Basic Scenario

Imagine a simple inventory list:

1. Enter the recommended formula in C2.

=LET(
    txt,A2,
    numText,REGEX.EXTRACT(txt,"[0-9]+([.,][0-9]+)?"),
    number,VALUE(SUBSTITUTE(numText,",",".")),
    unit,TRIM(SUBSTITUTE(txt,numText,"")),
    CHOOSE({1,2},number,unit)
)

2. As soon as you hit Enter, Excel “spills” the number into C2 and the unit into D2.
3. Drag down, or simply let dynamic arrays auto-fill if you convert the list into a table.
4. Format the new number column as General or Number—now 250 kg behaves like 250.
5. Run SUMIFS on the numeric column while filtering by the unit column; you can instantly total all items measured in kilograms.

Why it works
REGEX.EXTRACT is greedy enough to capture decimals but stops at the first non-numeric, leaving the unit intact. By substituting commas with dots you guarantee that VALUE interprets “12,5” correctly, regardless of system locale.

Troubleshooting

• Result shows #N/A? Confirm that the pattern actually finds a number—empty rows will error out. Wrap in IFERROR to return blank instead.
• Number turns to date? Reset the column’s format to General. Dates appear when Excel mis-interprets “12.3” as 12 March.

Variations

• Units preceding numbers (“kg 250”) can be solved by switching to REGEX.EXTRACT(txt,"[0-9]+([.,][0-9]+)?$") for the trailing number.
• Integers only? Use simpler pattern "[0-9]+" for performance.

Example 2: Real-World Application

A purchasing department receives a CSV from suppliers:

Goal: Convert everything to kilograms for cost comparison.

1. Place the data in [A2:A6].
2. In B2, split number and unit with the standard formula. You now have numbers in B and units in C.
3. Build a lookup table [E2:F4] mapping units to conversion factors: “kg” → 1, “g” → 0.001, “lb” → 0.453592.
4. In D2, multiply:

=B2*XLOOKUP(C2,$E$2:$E$4,$F$2:$F$4)

5. Copy down. Every supplier line now converts to kilograms uniformly.
6. With clean values, pivot the report by supplier, sum total kilograms, and join with pricing tables for margin analysis.

Business impact
What once took an hour of manual editing now updates automatically whenever a new CSV drops in the folder. The same technique feeds directly into Power Query or Power BI for enterprise reporting.

Performance considerations
The dataset may grow to tens of thousands of rows. REGEX.EXTRACT is vectorised, so it handles large arrays efficiently, but turn off screen updating during refresh and consider placing formulas outside the table into helper columns to preserve column order.

Example 3: Advanced Technique

Scenario: A laboratory records compound concentrations like “5 mg/mL” in one cell. You need two splits—numerator and denominator units—plus the numeric value.

1. Data lies in A2.
2. Use a three-stage LET:

=LET(
    txt, A2,
    num, VALUE(REGEX.EXTRACT(txt,"[0-9]+([.,][0-9]+)?")),
    unitPair, TRIM(SUBSTITUTE(txt,REGEX.EXTRACT(txt,"[0-9]+([.,][0-9]+)?"),"")),
    numerUnit, TEXTBEFORE(unitPair,"/"),
    denomUnit, TEXTAFTER(unitPair,"/"),
    CHOOSE({1,2,3}, num, numerUnit, denomUnit)
)

3. The formula spills: number in column B, numerator unit in C, denominator unit in D.
4. Add calculated columns for unit conversion or dimensional analysis.

Edge case handling

• If the string lacks a slash, TEXTAFTER returns #N/A. Wrap with IFERROR.
• Multiple decimals such as “3.5e-3 mol/L” require a wider regex pattern "[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?".

Professional tips
Create a named formula “SplitConcentration” in the Name Manager so colleagues can reuse it with a single argument: =SplitConcentration(A2).

Tips and Best Practices

1. Convert the raw list into an Excel Table before adding formulas. Tables automatically extend formulas to new rows, guaranteeing splits stay in sync.
2. Name your dynamic arrays. Calling the numeric spill “QtyValue” and the unit spill “QtyUnit” improves readability in downstream formulas.
3. Cache conversion factors in a separate sheet and reference them with XLOOKUP. Centralising factors minimises maintenance.
4. When performance matters, use simpler patterns like "[0-9.]+" if decimals always use dots. Avoid unnecessary parentheses in regex.
5. Protect your sheet but unlock the column where values arrive. Users can paste data freely without breaking formulas.
6. Document assumptions (decimal separator, single number per cell) in an adjacent comment or in the file’s “About” worksheet.

Common Mistakes to Avoid

1. Using LEFT and RIGHT without knowing where the number ends. This fails when numbers have variable digit lengths. Always locate the split position dynamically.
2. Forgetting to convert comma decimals. VALUE("12,5") depends on Windows locale and may mis-fire; explicitly replace commas with dots.
3. Hard-coding units into formulas (e.g., dividing by 1000 inside the main split). When a new unit appears, the formula breaks. Keep conversions separate.
4. Ignoring trimming. Extra spaces cause lookup failures and duplicate values in pivots. TRIM (or TEXTSPLIT with the " " delimiter) eradicates hidden spaces.
5. Leaving error values visible. Use IFERROR or LET-based conditionals to blank out header rows and keep dashboards clean.

Alternative Methods

When to switch

• If colleagues run Excel 2013, pick the MID approach or deploy the Power Query split-by-digit-transition feature.
• For server-side processes, Power Query or VBA integrates with scheduled refreshes.
• Use Text-to-Columns only when cleaning a legacy file once and never updating it.

FAQ

When should I use this approach?

Deploy the REGEX.EXTRACT method when you expect incoming data every day, need them instantly available for reports, and run Excel 365 or 2021. It shines in automated dashboards, lab notebooks, and procurement trackers.

Can this work across multiple sheets?

Yes. Point the formula’s txt argument to another sheet:

=LET(txt,Sheet2!A2,...)

Dynamic arrays will still spill in the current sheet. If you need both outputs in the source sheet, paste the identical formula there or wrap it in a MAP function referencing the remote range.

What are the limitations?

REGEX.EXTRACT finds only the first match. Cells containing two numbers like “6 ft 2 in” require a more elaborate pattern or multiple formulas. Also, very long strings (above 32 768 characters) hit Excel’s text limits.

How do I handle errors?

Wrap the final CHOOSE in IF(ISNUMBER(number),CHOOSE(...),"") or simpler IFERROR. For audit trails, redirect errors to a log sheet for manual review rather than suppressing them completely.

Does this work in older Excel versions?

Without REGEX.EXTRACT, use the MID loop formula in the Alternative Methods section, or run Text-to-Columns. Another option is to add the free “RegexTools” VBA module which back-ports regex to legacy Excel.

What about performance with large datasets?

On 50 000 rows, a single spill formula calculates once, whereas 50 000 individual row formulas compute 50 000 times. Therefore, prefer one dynamic spill wherever possible. Switch calculation mode to Manual during heavy imports, and avoid volatile functions like NOW() on the same sheet.

Conclusion

Splitting numbers from their units transforms messy, human-readable strings into structured, machine-readable data that unlocks the full analytical power of Excel. By mastering the dynamic REGEX.EXTRACT technique—and knowing fallbacks for older versions—you gain a repeatable solution that scales from a small parts list to enterprise-grade datasets. Add conversion lookups, clear documentation, and error trapping, and you will turn what was once tedious data cleaning into an automated, reliable pipeline. Keep practising on real files, explore Power Query for even richer workflows, and soon this skill will become a seamless part of your professional Excel toolkit.