Changing format of numbers

Bluemegaman

New Member
Joined
Nov 17, 2005
Messages
7
Hi all,

Having problems with changing the format of some numbers (they're supposed to be telephone numbers!). when exporting into a spreadsheet from our database some of the telephone numbers (but not all!) miss their first zero so in excel they come out in one number like

208XXXXXXX

I Need them like this

020 xxxx xxxx

I have thousands of numbers with this problem. Am in need of some code which will add the zero to the front end of the numbers and then put a space after the third and seventh digit, otherwise excel just deletes the zero again :cry: .

I have tried using the Left function etc but am not very familiar with these as i only started vba last week.

Any help would be very appreciated.

Thanks - :)
Mike
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
this formula will convert any number to your format. It has to be 11 characters though.

=REPT(0,11-LEN($A8)) & LEFT($A8,3-(11-LEN($A8))) & " " & MID($A8,4-(11-LEN($A8)),4) & " " & RIGHT($A8, 4)
 

Bluemegaman

New Member
Joined
Nov 17, 2005
Messages
7
thanks it would work but my numbers aren't always 11 characters long- some are missing the zero at the front which is why excel is having a problem displying them.

Preferably i need code which would search the numbers and find those without a zero on the front THEN convert them into the xxx xxxx xxxx format.
 

Bluemegaman

New Member
Joined
Nov 17, 2005
Messages
7

ADVERTISEMENT

yes i have tried formatting the cells that way and it does not take unfortunately :cry:
 

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
bluemegaman I meant with my formula the number cannot be longer than 11 characters. Shorter numbers are no problem. As long as you do not use phone numbers with 11+ cyphers you can use my formula.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,285
Messages
5,571,310
Members
412,381
Latest member
RogerL
Top