I have been working with telephone numbers recently. As phone numbers are unique to individuals they are useful IDs to match two lists. However some can be formatted as numbers, some as text. This makes it hard to compare numbers in different lists.

I have a phone number 01234 567890, in Excel it will be displayed as 01234 567890 with the space. However  if it was entered as 01234567890, Excel will display it as 1234567890, without the leading zero. If the data has been read in from a text fill in could be formatted as text and still show 01234567890.

When I get data in from an unknown source, I use two functions to help me get a number that is usable in any situation.

First I substitute any spaces =SUBSTITUTE(A3,” “,”"). This will remove any spaces at the beginning, middle or end.

Then I format the number to text =TEXT(B3,”00000000000″). This gives me 01234567890 in my cell. Note the 11 zeros in the text function. this gives me the leading zero for phone numbers.

I do of course do all this in one cell =TEXT(SUBSTITUTE(A3,” “,”"),”00000000000″). I can then use this result as the lookup to compare to other lists and match up phone numbers.

Indenting text in cells without adding spaces

There are times you will have a column of text immediately to the right of a column of numbers they both look squashed.

Too many people insert spaces at the beginning of each of their text entries to provide some ‘white space’. There are various reasons why this is not the best option, including the fact that it is very time consuming.

There is a much easier way easier way to achieve the same result by formatting the cells.

• Select the cells that contain the data you want indented;
• From the Format menu select Cells;
• Click on the Alignment tab and select Left (Indent) from the Horizontal dropdown list.
  On the Indent spinner box set the number of spaces you want to insert between the start of your text and the left edge of the the column;
• Click OK and you’re done.