Urgent excel number formatting help

Cdunlo11

New Member
Joined
May 11, 2014
Messages
4
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 :(
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

GCExcel

New Member
Joined
Apr 11, 2013
Messages
48
Hi,
Assuming all the number have 11 digits, why not try a formula :
Code:
=LEFT(SUBSTITUTE(A1," ",""), 5) & " " & RIGHT(SUBSTITUTE(A1, " ", ""), 6)
 

Cdunlo11

New Member
Joined
May 11, 2014
Messages
4
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,197
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

New Member
Joined
May 11, 2014
Messages
4

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,197
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

New Member
Joined
May 11, 2014
Messages
4
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,197
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,137,154
Messages
5,679,908
Members
419,862
Latest member
Bluewings666

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top