Formula

Hello Susan,

Further to my email I give below the steps required to remove spaces and add leading zeros.

1) Add 2 columns after telephone number (Column C & D)

2) Select the data in the Telephone Number column and use the Text to Columns feature in the Data ribbon to split the data into as many columns as there are spaces

3) Select cell D2 and create the following formula:

=CONCATENATE(B3,C3) This joins the numbers together for 2 columns only

4) Copy down and then select all items if necessary, and copy... follow with paste values (this removes the CONCATENATE formula and leaves the number formatted as text) name D1 as Telephone Number

5) Make sure all items are selected and you will notice a green triangle in the top left corner of each cell. To the left of the top cell click the Smart Tag and select the Convert to Number option

6) Still with everything selected go to Format Cells select the Number tab and click Custom

7) In the Type field enter 11 zeros and click OK (This will add a zero to the front of each number)

8) Delete Column B & C leaving you with Column D which now becomes B

Since you probably have to do this on a regular basis you would want an automated system to help. The best way to achieve this is to record a macro of all the steps saving it into the Personal Macro Workbook. This is beyond the scope of this forum question if you are unable to create macros. Ask someone at your office to help you with this or attend our Advanced Excel course.


I hope this resolves your question. If it has, please mark this question as resolved.

If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?

Have a great day.
Regards,

Rodney
Microsoft Office Specialist Trainer