Text
Split numbers from units of measure ("12.5 kg", "2500mg")
Separate an unsigned decimal numeric prefix from a trailing alphabetic unit in controlled measurement strings.
Strip digits and decimal points to isolate any unit suffix
Lab and BOM sheets mix spaced ("12.5 kg") and unspaced ("2500mg") measurements, so splitting on " " fails on half the rows.
Once spaces are removed (s = "12.5kg"), stripping characters "0"–"9" and "." leaves ONLY the trailing unit u = "kg"! And because s consists of [number] + [unit], the numeric prefix has exact length LEN(s) - LEN(u), so VALUE(LEFT(s, LEN(s) - LEN(u))) extracts 12.5 or 2500 cleanly.
| RawMeasure (A) | Extracted Number | Extracted Unit |
|---|---|---|
| 12.5 kg | 12.5 | kg |
| 2500mg | 2500 | mg |
| 400 ml | 400 | ml |
Number-extractor and unit-extractor formulas
Handles repeated digits ("2500"), decimals ("12.5"), and multi-letter units ("kg", "mg", "psi", "kWh") without regex.
=LET(s, SUBSTITUTE(A2," ",""), u, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(s,"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9",""),".",""), VALUE(LEFT(s, LEN(s) - LEN(u))))Isolate the trailing unit text
Returning u directly gives the clean unit string ("mg" for "2500mg").
=LET(s, SUBSTITUTE(A3," ",""), SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(s,"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9",""),".",""))Inputs and interpretation
Assume an unsigned number with at most one dot decimal point followed only by alphabetic unit characters. This digit-stripping method is not a general measurement parser: negative signs, exponents, decimal commas, fractions, m2, or unit digits break the length calculation. Removing spaces can also turn malformed 1 2 kg into 12kg. Validate input first; no unit conversion is performed. VALUE decimal interpretation can depend on Excel locale.
Try the example
Use the original values or change them to test your understanding.