reformat phone no list - macro?

[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!

 

[Pete_UK]

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

 

[Silvabod]

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

 

[Dave Peterson]

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

 

[Pete_UK]

Thanks for the feedback - glad it worked for you.

Pete