Listing Telephone Number – disappearing leading zeros?

Listing Telephone Numbers

Do you have a problem inputting the correct telephone numbers into your Excel spreadsheet?

You probably lose the leading zeros?  Excel sees data we place in a cell in one of three ways:

 NUMBERS, TEXT, or FORMULAS

Excel sees the phone number you are typing in as a NUMBER. Logically no number starts with a zero, so Excel very kindly removes it for you! Thanks a bunch Excel.

So our “telephone number” data has to be seen by Excel as TEXT.

To do this a TEXT “Character” needs to form part of the data in the cell.

Some TEXT “Characters” such as “+”, “/”, and “*” Excel sees as associated with NUMBERS. Most other TEXT characters placed within a set of numerical characters will turn NUMBERS into TEXT.

The classic method to show NUMBERS as TEXT is to place an Apostrophe “” (under the “@” key) in front of the numerical characters. See the example above. Note that the Apostrophe is not visible in the cell.

Another method to “kid” Excel is to use a SPACE. This should be placed somewhere after the first character, as similar to zeros, leading spaces are ignored by Excel.

 

 

See example above.

For consistency I put my space in after the fifth character. This makes the number easier to read, and is similar to the way we give out our number; “01908”, pause, “456123”.

About Derek