“N/A” and dashes in a column that should be numbers

A numeric column contains “N/A”, “-”, “n.a.”, or “(blank)” alongside real figures. Totals are wrong or the import refuses the whole column.

Why this happens

  • These are all a human writing “nothing here” in a cell that has no way to say it. A spreadsheet already has a representation for absent, which is an empty cell, but a person filling in a form by hand tends to want a visible mark that they did not skip the row.
  • They do not all mean the same thing, which is what makes replacing them with zero dangerous. “N/A” usually means the question does not apply, a dash often means genuinely nothing, and “TBC” means a real value exists and is not known yet. Zero means the value is known and is zero.
  • There are several dash characters and they are not interchangeable to a computer. A hyphen, an en dash, and an em dash all read as a dash to you and as three different strings to anything comparing text.

What to do right now

  • Decide what absent should mean for this column before touching anything, and write it down. Whether a missing quantity blocks the row or defaults to zero is a business decision, not a formatting one.
  • Never blanket-replace placeholders with zero in a column that is summed or averaged. An average over rows that were not applicable is a number with no meaning.
  • Search for each dash character separately, or match a character class covering all of them. A find-and-replace for the one on your keyboard misses the two that came from a word processor.

Stopping it on the next file

The common placeholders are recognised as blank rather than as text: an empty cell, a hyphen, an en dash, an em dash, a double hyphen, “n/a”, “n.a.”, “(blank)”, and “(empty)” are all read as nothing there. What happens next is then your schema's decision rather than a guess. A required field refuses the row and names it, and an optional one accepts the gap as a gap, which is the honest recording of what the sender actually told you.

The inventory count template is a sheet with those rules already set, if you want to see the shape of one before building your own.

Other things that go wrong

My CSV columns are shifted or split in the wrong placesMost rows look right, but some have values in the wrong columns: a postcode in the country column, a phone number where the email should be. The rows that are wrong all seem to have something in common.My supplier's prices are a thousand times wrongPrices from a European supplier are out by a factor of about a thousand, or occasionally by a factor of a hundred. Nothing errored. The totals are simply wrong, and some individual rows look perfectly reasonable.The plus sign disappeared from my phone numbersInternational phone numbers have lost their leading plus. Some cells show an error instead, and some have collapsed into a single run of digits with the spaces gone.My CSV shows é where it should show éNames and addresses are full of sequences like é, ü, or ’ where accented letters, umlauts, and apostrophes should be. Sometimes there are question marks or black diamonds instead.

Define the sheet once

Name your fields, and every file after that is matched to them and checked cell by cell before a row is allowed to land.

Start freeAll fixes