I have several phone numbers that I need to add spaces for.
If the number starts with 02 then I need a space between the third and 4 character and a space between the 7th and 8th character. So the number looks like:
020 1234 5678
And another query that if the number starts with 08 then the space should be after the 4th character and should look like:
0812 34567891
can someone help?
If the number to be converted is in cell B11, then this formula in an adjacent cell will convert it:
There's no error checking there, we assume they WILL be 11 or 12 digits and they WILL start with either 02 or 08.
=IF(LEN(B11)=11,IF(LEFT(B11,2)="02",LEFT(B11,3)&" "&LEFT(RIGHT(B11,8),4)&" "&RIGHT(B11,4)),IF(LEN(B11)=12,IF(LEFT(B11,2)="08",LEFT(B11,4)&" "&RIGHT(B11,8),B11),B11))
Also, this will only work if the cell is formatted as text. Will that work for you?
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks