Insert spaces in excel number sequences

Question

I am trying to format cells so when I type in phone numbers it automatically formats them as follows: -

Landline Nos e.g. 02012345678 to appear as 020 1234 5678

Mobile Nos e.g. 07123456789 to appear as 07123 456 789

I was trying to use conditional formatting for text strings beginning with "020" or "07" but when I type the custom number format in as ### #### #### it wouldn't insert the spaces. I'm probably doing something really stupid so any help would be greatly appreciated.

Thanks.

Solution

Go to the cell.

Press CTRL+1 to open the Format Cell.

Go to the Number Tab

Under "Category", select "Custom"

Where it says "Type", enter 0## #### ####

This should ensure that you get your leading zero, and that the spaces are added.

Excel also has a format under the "Special" category just for Phone Numbers. If you click that, then click "Custom" it will insert the Phone format which you can then edit as you see fit.

Comments

[Raystafarian]

Works for me, cannot reproduce. How are you choosing the format? What version excel?

[armani]

Same here, just tried what you did and it worked for me.

[BigStevie1973]

I am using Excel 2013. I go into "New rule" - "Format only cells that contain" - "specific text" - "beginning with" and then I put in "020" click on format, go to custom and set ### #### #### as the format. Under preview it comes up as "3 8718" which doesn't seem to make sense.

[Raystafarian]

Are you using the quotation marks in the rule? Also the rules name doesn't matter if it's working correctly. Also if you specify a number of characters that isn't the number of characters in the cell, it will extrapolate.

[BigStevie1973]

No, I'm not using the quotation marks in the rule. The number of characters is always 11 so should be ok. Should I maybe use wildcards in the rule e.g. beginning 020????????

[BigStevie1973]

I tried this and works as a one off but I could not get it to work as conditional formatting as it kept removing the leading zero. I also tried the special category for phone numbers but couldn't get this to work either.

[Anthony A Gaff]

I am too new to add a comment to your post below, but I'm glad you found a fix for this. I was just going to suggest that since it's a number, you can't check for the leading zero. You could only find that if it were a string/text value. Great work! Thanks for posting your update.

[Burgi]

The OP has suggested in the comments he does not want to use speech marks.