Urgent excel number formatting help

[Cdunlo11]

Hi all,


Im having an issue formatting telephones basically I've a large list of mobile and landline numbers in different formatted e.g some are 0741 1088291 others are 07 411 088 291 and more I need them to be 07411 088291 and the same with land lines. I have used different variations of custom and special formatting however I can only get one style to work and the rest don't change . Please help me

 

[GCExcel]

Hi,
Assuming all the number have 11 digits, why not try a formula :

=LEFT(SUBSTITUTE(A1," ",""), 5) & " " & RIGHT(SUBSTITUTE(A1, " ", ""), 6)

 

[Cdunlo11]

Hi
Thanks for the reply, I've tried that but with no luck, I'm doing this on my phone so don't no if it's loaded correctly, just to check should I have put anything in between the "" or anything? My knowledge of excel is average sorry

thanks
c

 

[Rick Rothstein]

Hi all,

Im having an issue formatting telephones basically I've a large list of mobile and landline numbers in different formatted e.g some are 0741 1088291 others are 07 411 088 291 and more I need them to be 07411 088291 and the same with land lines. I have used different variations of custom and special formatting however I can only get one style to work and the rest don't change . Please help me

What about selecting the column with those numbers in it, calling up Excel's Replace dialog box, put a single space in the "Find what" field, leave the "Replace with" field empty, click the "Options>>" button and make sure there is NO checkmark in the checkbox labeled "Match entire cell contents", then click the "Replace All" button. After that, you can Custom Format your cell using this Type pattern...

00000 000000

 

[Cdunlo11]

Hi rick

Really strange I've tried that with a small batch of 7 records to test and it says found and replaced 35 records yet nothing changes :/

 

[Rick Rothstein]

Hi rick

Really strange I've tried that with a small batch of 7 records to test and it says found and replaced 35 records yet nothing changes :/

Try performing a second replace, this time remove the space you typed in earlier and then, with the cursor still in the "Find what" field, hold down the ALT key while typing the following digits on the Number Keypad only (do NOT type them from the main keyboard)...

0 1 6 0

still leave the "Replace with" field empty and then click the "Replace All" button. Now try applying the Custom Format and see if that makes a difference.

 

[Cdunlo11]

Really
Sorry but it's now saying Microsoft can't fine any data to replace :/ the issue only appears to be happening with data with 3 spaces if that helps?

 

[Rick Rothstein]

Really sorry but it's now saying Microsoft can't fine any data to replace :/

That is okay... it just means my hunch was wrong (I thought maybe you had "non-breaking spaces" in your text which look like spaces but that have a different ASCII code number).

...the issue only appears to be happening with data with 3 spaces if that helps?

I am not sure what to tell you as the procedure I outlined originally works for me here on my copy of Excel.