Let’s say you are getting inputs you can’t control and in some cases you get text and others you get numbers. You want the numbers, but you need to treat text as zero. Here’s the easy way to do that.

There is a little used function in Excel called N. It converts text into a zero. Numbers remain the same. See the image below.

If you are dealing with either numbers or text then the humble, and extremely short, N function does the trick. The formula in cell C2 is

=N(B2)

Converting text number and text dates

If you need to handle text numbers and text dates as, numbers and dates respectively, the N function doesn’t provide the solution – see image below.

The number in cell C4 is the underlying number for the date 1/1/2020. If you apply a date format to cell C4 it will look correct.

Note: Left aligned entries (when no alignment formatting has been applied) like B5 and B6 are treated as text by Excel.

As you can see rows 5 and 6 are treated as text and return zeros by the N function.

To have rows 5 and 6 return their respective number/date you need to use the IFERROR function. See image below.

The formula in cell D2 is

=IFERROR(B2*1,0)

Whilst not as short, this function will convert anything that looks like a number or a date into a number or a date. Multiplying a text number/date by 1 ”coerces” (forces) it into number.

If you multiply text by 1 it returns an error. Hence we used a zero on the end of the IFERROR function. Any text that can’t be converted into a number or a date will return a zero.

Other Text Dates

Not all text dates will be converted when you multiply by 1 –  see row 7 in the image below.

The IFERROR technique handles most situations, but not all.