Format phone numbers

**filip.verborgh@gallo** · 03-18-2011, 06:41 PM

Hello,

New here.
I have to convert a lot of phone numners (eg. +32498871699) into a more readable format (eg. +32 498 87 16 99).

How can I do this without having to edit all the numbers and add the spaces manually?
All telephone numbers are stored as text.

I use Excel 2010 (Dutch version, English language pack available).

Thanks a lot!

Filip

**Cutter** · 03-18-2011, 06:48 PM

Are you familiar with the LEFT(), MID() and RIGHT() functions?

http://www.excelfunctions.net/LeftFunction.html

http://www.excelfunctions.net/MidFunction.html

http://www.excelfunctions.net/RightFunction.html

**TMS** · 03-18-2011, 06:48 PM

=MID(A2,1,3)&" "&MID(A2,4,3)&" "&MID(A2,7,2)&" "&MID(A2,9,2)&" "&MID(A2,11,2)

Regards

**Cutter** · 03-18-2011, 06:52 PM

Or

=LEFT(A1,3)&" "&MID(A1,4,3)&" "&MID(A1,7,2)&" "&MID(A1,10,2)&" "&RIGHT(A1,2)

**shg** · 03-18-2011, 06:54 PM

If you enter the numbers just as numbers rather than text, you can custom-format them as +00 000 00 00 00

**TMS** · 03-18-2011, 06:56 PM

@Cutter: I just thought the consistent use of MID throughout was aesthetically pleasing ;-)

I'm not sure if there's any trade off in efficiency.

Regards

**Cutter** · 03-18-2011, 07:05 PM

@TMShucks

You're right - it's very pretty!

I just thought I should justify the links I provided.

I doubt there's an efficiency difference.

**TMS** · 03-18-2011, 10:23 PM

@Cutter: Bless, there you were, going into full coaching mode and encouraging the OP to work it out for him/herself. and I steamed in and blew it out the water. Sorry ;-(