The Cryptic T() and N() Functions to Force Text and Numbers

Say that you set up a spreadsheet for general use.  You expect the user to type text into certain cells, which you then use in formulas.  But they leave some cells blank.  If your text formulas refer to those cells, they read as zero, which messes up your text.  Use =T(cell) to return an empty string instead of 0.

Or there is a cell where the user should enter a number, but they could enter text instead.  The text would cause an error in formulas.  You can force it to a numeric value (zero) using =N(cell).  This might be dangerous, so use with caution.

Syntax

T(Text): Returns text if the argument is text, returns a blank string (“”) for numbers.

N(Value): Returns the number if the argument is numeric, returns zero for text.

Example for T(): Treat Blanks as Blanks Instead of Zero

In column D, we have formulas (documented in column E) that refer to the corresponding cells in column B.

Joe does not live in an apartment (Soapville has no blocks of flats), so he has left that block blank.  Soapville is also too small to have suburbs, so Joe has left that block blank, too.

Those blank blocks then show as zeros in column D.  Excel “helpfully” interprets a blank cell as 0.

In column G we have fixed the problem by using the =T() function to refer to the cells in column B.  Where the function finds an empty cell, it returns an empty string (“”) instead of zero.

Result: A cleaner, more professional-looking form.

Side Note:  To allow it to keep the leading zeros, we preformatted cell B11 as Text. (Press Ctrl+1; on the Number tab, choose Category = Text.)  One can also use this trick for telephone numbers, SKU numbers, and other non-numeric items composed of numbers.

Example for N(): Force Text to Zero and Avoid an Error

Cell A4 contains text.  Using it in a formula produces the #VALUE! error.

Wrapping the cell address in the =N() function means that Excel interprets the text as zero.

The N() function does not interpret the text “forty-two” as the numeric value 42, an obvious lack of foresight on the part of Microsoft.

Apply this with caution!  It may be better to show the error rather than simply work around it.

Bonus Tip: Remember, you can check the contents of a cell using the functions ISBLANK, ISNUMBER and ISTEXT.  Then give appropriate messages using the IF function.  For example, =IF(ISTEXT(Entry), “<< Please enter a number, not Text!”, “”)