Parse Numbers with Units in Excel Cells

Spreadsheets filled in by people store quantities the way people write them: "12 kg", "1,200 g", "2.5t", "0.5 tonnes", "500 ml". A product sheet assembled from several suppliers might mix all of these in one column, alongside plain numbers and the occasional "approx. 3 kg". Excel treats every one of them as text, so nothing can be summed, sorted or compared. Converting them needs two decisions per cell — what the number is, and what unit it is in — and then a conversion to one unit for the whole column. This page builds that with a regular expression and a unit table in pandas, handles abbreviations like "k" and "M", decimal commas and missing units, shows when the pint library is the better tool, and reports every cell it could not parse instead of silently dropping it. It belongs to Extracting Data from Messy Excel Sheets.

Prerequisites

pip install pandas openpyxl pint

products.xlsx lists twelve products from several suppliers. The Weight column mixes kilograms, grams, tonnes and pounds, with and without spaces, in different case, with a decimal comma in one cell, a plain number in another, an "approx." prefix and one blank. Volume mixes litres and millilitres. Annual units uses "k" and "M" suffixes and thousands separators.

Step 1 — Split each cell into value and unit

A single regular expression captures an optional leading word, the number (with either decimal separator) and the unit after it:

import re
import pandas as pd

df = pd.read_excel("products.xlsx", dtype=str).fillna("")
PATTERN = re.compile(r"^\s*(?:approx\.?|about|~)?\s*([-+]?\d[\d.,\s]*)\s*([a-zA-Z]*)\s*$")

def split_value(text):
    m = PATTERN.match(str(text))
    if not m:
        return None, None
    number, unit = m.groups()
    return number.strip(), unit.lower() or None

parts = df["Weight"].apply(lambda v: pd.Series(split_value(v), index=["number", "unit"]))
print(pd.concat([df["Weight"], parts], axis=1).to_string(index=False))

Reading with dtype=str keeps every cell as written, including the plain number 15, which arrives as the text "15". The pattern allows an optional "approx.", "about" or "~" prefix, then a number that may contain digits, dots, commas and spaces, then letters for the unit. Units are lower-cased so "KG" and "kg" are the same. The blank cell produces no match, which is the correct result: there is no value. Keeping the raw number text at this stage, before converting it, means the decimal-separator question can be answered separately in the next step.

Step 2 — Convert the number text, decimal commas included

"1,200" is twelve hundred; "0,75" is three quarters. A simple rule distinguishes them: a comma followed by exactly three digits at the end is a thousands separator, otherwise a lone comma is a decimal separator:

def to_number(text):
    if text is None:
        return None
    t = text.replace(" ", "")
    if "," in t and "." in t:                       # 1,234.5 or 1.234,5
        t = t.replace(",", "") if t.rfind(".") > t.rfind(",") else t.replace(".", "").replace(",", ".")
    elif "," in t:
        t = t.replace(",", "") if re.fullmatch(r"\d{1,3}(,\d{3})+", t) else t.replace(",", ".")
    return float(t)

for sample in ["1,200", "0,75", "1.234,5", "1,234.5", "25", "2.5"]:
    print(f"{sample:>8} → {to_number(sample)}")

When both separators appear, whichever comes last is the decimal point. A lone comma followed by groups of exactly three digits is a thousands separator; anything else — "0,75", "2,5" — is a decimal comma. The rule is ambiguous for a value like "1,500", which could be either; in a column that otherwise uses decimal points, thousands is the right reading, but if your data is mostly European it may not be. When in doubt, read the column's other values or ask the source which convention they use. CSV files with decimal commas are covered in Read CSV Exports with Semicolons and Decimal Commas.

Step 3 — Convert to one unit with a lookup table

With value and unit separated, conversion is a multiplication by a factor from a table. Spelled-out forms and plurals map to the same factor; a missing unit gets the column's default:

TO_KG = {"g": 0.001, "kg": 1.0, "t": 1000.0, "tonne": 1000.0, "tonnes": 1000.0, "lb": 0.45359237, "lbs": 0.45359237}

def weight_kg(text, default_unit="kg"):
    number, unit = split_value(text)
    if number is None:
        return None, "unparsed" if str(text).strip() else "blank"
    unit = unit or default_unit
    if unit not in TO_KG:
        return None, f"unknown unit '{unit}'"
    return round(to_number(number) * TO_KG[unit], 4), "ok"

df[["Weight kg", "Weight status"]] = df["Weight"].apply(lambda v: pd.Series(weight_kg(v)))
print(df[["SKU", "Weight", "Weight kg", "Weight status"]].to_string(index=False))

Every weight now has a value in kilograms: 1,200 g becomes 1.2, 2.5 t becomes 2,500, 350 lbs about 158.8, and the bare "15" is read as 15 kg by default. The status column says what happened to each cell — "ok", "blank", or a specific problem — so nothing disappears silently. Writing the factors out as a table, with the exact pound-to-kilogram factor, makes the conversion reviewable by someone who does not read code. The default unit for bare numbers is a real decision: if the column header said "Weight (kg)" it is safe, if it said "Weight" it deserves a flag rather than an assumption.

Step 4 — Suffixes like k and M

The annual-units column uses a different convention: magnitude suffixes rather than physical units. The same split works with a different table:

SCALE = {None: 1, "k": 1_000, "m": 1_000_000, "bn": 1_000_000_000}

def scaled(text):
    number, suffix = split_value(text)
    if number is None or suffix not in SCALE:
        return None
    return int(round(to_number(number) * SCALE[suffix]))

df["Annual units n"] = df["Annual units"].apply(scaled)
print(df[["SKU", "Annual units", "Annual units n"]].to_string(index=False))
print("total annual units:", f"{df['Annual units n'].sum():,}")

"3.5k" becomes 3,500, "1.2M" 1,200,000, and "4,500" — a thousands separator, no suffix — 4,500. Note that "m" means million here but metre in a length column: a unit table belongs to a column, not to the whole sheet, which is why each column gets its own. Rounding to an integer suits counts; keep floats for anything that can be fractional.

Step 5 — Let pint handle the units, and report the failures

For columns with many different units, or units with prefixes and compound forms, the pint library knows thousands of units and does the conversion itself. Use the same split, then hand the unit to pint:

import pint

ureg = pint.UnitRegistry()

def litres(text):
    number, unit = split_value(text)
    if number is None:
        return None
    try:
        return round(ureg.Quantity(to_number(number), unit or "l").to("liter").magnitude, 4)
    except pint.errors.UndefinedUnitError:
        return None

df["Volume l"] = df["Volume"].apply(litres)
print(df.loc[df["Volume"] != "", ["SKU", "Volume", "Volume l"]].to_string(index=False))

problems = df.loc[df["Weight status"].isin(["unparsed"]) | df["Weight status"].str.startswith("unknown"), ["SKU", "Weight", "Weight status"]]
print("weights needing attention:", problems.to_dict("records") or "none")

pint converts "250 ml", "2.5 L" and "310ml" to litres without a hand-written table — it knows that ml is millilitre and L is litre. It is the better choice when units are varied or scientific; a small explicit table is clearer when the column only ever has three or four units, and it lets you decide what ambiguous abbreviations mean. Either way, end with a list of everything that did not convert. Here every weight parsed — including the "approx." one — so the list is empty; a cell like "two bags" would appear in it with status "unparsed" and go back to the supplier rather than vanish.

Common pitfalls

• One unit table for the whole sheet. The same letters mean different things in different columns.
• Guessing decimal commas. Apply a clear rule and check ambiguous values like "1,500".
• Bare numbers assumed to be in some unit. Decide the default per column and document it.
• Dropping cells that do not parse. Record a status and report them.
• Keeping the original text only. Store the converted number in a new column and keep the original for audit.

Conclusion

To parse numbers with units from Excel cells, read the column as text, split each cell into a number and a unit with a regular expression, convert the number text with an explicit rule for thousands and decimal commas, and multiply by a factor from a per-column unit table — or let pint do the conversion when units are varied. Treat magnitude suffixes such as k and M as a table of their own, give bare numbers a deliberate default unit, and record a status for every cell so that anything that failed to parse is reported rather than lost.

Frequently asked questions

How do I convert "12 kg" text to a number in pandas? Split the text into the number and the unit with a regular expression, convert the number with float after handling separators, and multiply by a conversion factor for the unit.

How do I convert mixed units like g, kg and t to one unit? Use a dictionary mapping each unit to its factor in the target unit, such as g to 0.001 and t to 1000 for kilograms, or use the pint library to convert.

How do I convert values like 3.5k or 1.2M to numbers? Split off the suffix and multiply by its magnitude: k for thousand, M for million.

How do I handle decimal commas such as 0,75 kg? Treat a comma followed by groups of exactly three digits as a thousands separator and any other single comma as a decimal point, and check ambiguous values against the rest of the column.

Related

• Up one level: Extracting Data from Messy Excel Sheets — getting data out of awkward layouts.
• Extract Numbers and Codes from Text with pandas — numbers buried in longer text.
• Convert Excel Text Columns to Numbers with pandas — numbers stored as text.
• Convert Percentage Strings to Numbers with pandas — another unit-like suffix.