reformat phone no list - macro?

  • Thread starter Thread starter Silvabod
  • Start date Start date
S

Silvabod

Have HUNDREDS of phone nos in a column, display example 01456 123456
Some bright spark wants them re-formatted so the exchange is in brackets,
eg (01456) 123456.
Can anyone suggest a macro for this task, please? Help would be much
appreciated!
 
The exchange code can be from 4 to 7 digits in length, although it is
my experience that a space is often inserted after the first 5
characters, as in your example. If your data is like this, then the
brackets won't necessarily encompass the exchange code.

Nevertheless, you could do what you want by this formula:

="("&LEFT(A2,FIND(" ",A2)-1)&")"&RIGHT(A2,LEN(A2)-FIND(" ",A2)+1)

which will look for a space to separate the number into two parts,
assuming it is in cell A2.

Hope this helps.

Pete
 
Thanks, Pete, this does the job.

Re "exchanges" - you are correct in your reservation. London (0207 or 0208)
and Nottingham (0115) are notable 4 digit examples !! Fortunately local (5
digit) exchange numbers are my only concern.
Thanks again
 
If the value in the cell is really
01456 123456
(with that space)

Then maybe you could use a formula like:
="("&SUBSTITUTE(TRIM(A1)," ",") ")
in an adjacent cell (and drag down)

If it works, select that column
edit|copy
edit|paste special|values over the original column
and delete that helper column
 
Back
Top