formula

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

• Home
• Courses
• Promotions
• Schedule
• Formats
• Our Clients

# Formula

Resolved · Low Priority · Version 2007

Susan has attended:
Excel Intermediate course

### Formula

Various people insert telephone numbers in systems with spaces, this is then presented on spreadsheet reports. The 0 also disappears on the front of some mobile phone numbers and some mobile numbers are presented on the data between digits, such as 7825 456789. Is there a quick way to add the '0' at the beginning (only on the numbers that are missing the 0) and remove the spaces?

### RE: formula

Hello Susan,

Hope you enjoyed your Microsoft Excel Intermediate course with Best STL.

Thank you for your question regarding removing spaces from a telephone number and adding a zero to the beginning of the string.

This is a question easier answered when I have your data in front of me. The reason is that I need to know what formatting the telephone numbers have. Until I know this I will be flying in the dark.

Open a new workbook and copy the column/s of telephone numbers from your data sheet into the new workbook and then email this to me at:

rl@stl-training.co.uk

Have a great day.
Regards,

Rodney
Microsoft Office Specialist Trainer

### RE: 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

Training courses

Training information:

 Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions What does 'Resolved' mean? Any suggestions, questions or comments? Please post in the Improve the forum thread.

### Counting Blanks

Some times you want to check if there are cells missing data in your range. You can use the COUNTBLANK FUNCTION to acheive this. It is =COUNTBLANK(Range). Note Cells with formulas that return "" (empty text) are also counted. Cells with zero values are not counted.

View all Excel hints and tips