Every data person has a version of this story. A report lands as a PDF, a dashboard, or a vendor email. The numbers look perfect — 12.5 kg, $1,240.00, 3,200 units, 0.75 L — so they get copied straight into a spreadsheet. Then the formulas run, and the column that should total quietly fills with #VALUE! errors and zeroes. The numbers were never numbers. They were text wearing number costumes.
Why pasted values are text in disguise
Spreadsheets decide what a cell is by its content. The moment a unit rides along — kg, $, units, %, L — the cell becomes text, and text does not add up. SUM() skips it. AVERAGE() ignores it. Sorting puts "100 kg" before "20 kg" because alphabetically 1 comes before 2. Currency symbols, thousand separators, and non-breaking spaces (the invisible hitchhiker of every web copy-paste) all have the same effect.
The mixed bag is the worst part: one column might hold "45", "45 kg", "$45", "45.0 kg", and "N/A" side by side. Each variant needs a different fix, and eyeballing a thousand rows is not a fix.
The formula gauntlet
The traditional escape route is formulas. =VALUE(SUBSTITUTE(SUBSTITUTE(A1," kg",""),",","")) works — for exactly the cells that look like the example. A second unit means a second nested SUBSTITUTE; a third means the formula is now a maintenance liability. Regular expressions help if your spreadsheet supports them (one pass for the digits, a second pass for the unit), but a regex that handles decimals, negatives, thousand separators, and scientific notation is a small project, not a formula.
Text-to-Columns can split on the space between number and unit, until it meets "1,240.00" (the comma becomes a split point) or a non-breaking space (which is not a space at all). Each of these fixes works on the data you have and breaks on the data you get next week.
A calmer habit
People who do this often settle on the same habit: never clean in place. Keep the raw pasted column untouched, do the extraction in new columns, and keep number and unit in separate homes from the start. One column holds the numeric value, typed as a number; the next holds the unit as text. Everything downstream — sums, charts, conversions — reads the numeric column and never has to guess. When the source changes its formatting next month, only the extraction step is rewritten, not the analysis.
The shortcut for the extraction step
The extraction step is the tedious part, and it is the part a small purpose-built tool does well. ToolBrewLab's free tool that splits a pasted number-and-unit value into a clean number and its unit takes values like "12.5 kg" and returns the number and the unit separately — no sign-up, runs in the browser, and nothing is uploaded. Paste the messy column through it once, and the spreadsheet finally gets real numbers to work with.
