Excel number formatting

Resolved · High Priority · Version 2010

Fri 24 Feb 2017, 16:09 Delegate Salima said...

Salima has attended:
Word Intermediate course
Word Advanced course
Excel Intermediate course
Excel Advanced course

Excel number formatting

Hi

I have a worksheet with lots of telephone numbers. They need to be formatted differently as they're numbers from different countries and include dialling codes etc. So for example, I might have 020 7666 6666 and 001 604 333 3333. How do I do this? It seems that the numbers lose their formatting. I want to be able to select a column of numbers and have them formatted the same way.

Thank you

Salima

Fri 24 Feb 2017, 16:42 Trainer Jens said...

RE: Excel number formatting

Hi Salima,

Thank you for the forum question.

You cannot have telephone numbers formatted as numbers in Excel. Excel will remove the start 0. Telephone numbers must be text formatted. When you write formatted the same way I guess you mean, all US numbers are formatted the same way, all UK numbers are formatted the same way. You want the telephone numbers "country" formatted

You do not have a tool in Excel which can do this, but if you can define exact rules (UK number 5 digits a space 3 digits a space 3 digits) it can be coded using vba.

I hope, I understood your question. If not please give me more details of what you want.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Fri 3 Mar 2017: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Closing Multiple Workbooks quickly

When you have several workbooks open in Excel and want to just close them all at once:

1) Hold down the SHIFT key before selecting the File menu.

2) Once in File menu release SHIFT key and select Close All option.

3) All your files will close. If files require saving Excel will ask if you want to save the changes.

View all Excel hints and tips