LinkBack URL
About LinkBacks
I have a long column of phone numbers. I need to put all the numbers into (xxx) xxx-xxxx format.
Currently they are in three different formats.
1) xxx-xxx-xxxx
2) xxxxxxxxxx
3) (xxx) xxx-xxxx
Thank you.
Gene
I have a long column of phone numbers. I need to put all the numbers into (xxx) xxx-xxxx format.
Currently they are in three different formats.
1) xxx-xxx-xxxx
2) xxxxxxxxxx
3) (xxx) xxx-xxxx
Thank you.
Gene
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",)," ",),"(",),")",) then format as (000) 000-0000
Please add reputation by clicking on the * if I have helped.
Please mark the thread SOLVED if your issue has been resolved.
Thanks, Glenn.
Try this:
=IF(ISERR(FIND("(",A1)),"("&LEFT(SUBSTITUTE(A1&"-","-",),3)&") "&MID(SUBSTITUTE(A1&"-","-",),4,3)&"-"&RIGHT(SUBSTITUTE(A1&"-","-",),4),A1)
Needs to be converted to a number at the end, so try this =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",)," ",),"(",),")",)*1
Try this
=REPLACE("("&REPLACE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"(",""),") ","")),4,,") "),10,,"-")
xxx-xxx-xxxx (xxx) xxx-xxxx xxxxxxxxxx (xxx) xxx-xxxx (xxx) xxx-xxxx (xxx) xxx-xxxx
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
I'm very new at this and I don't know how to begin.
Step 1: Highlight the column of phone numbers??
Step 2: Paste one of the formulas like:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",)," ",),"(",),")",)*1
into the fx box and hit enter??
All it does is replace the first phone number in the column with the formula.
??
1.) Paste the formula of your choice into an empty column next to the first phone number listed, then hit enter.
2.) Hover your cursor over the bottom-right corner of the cell that you just pasted the formula in until you see a small black plus sign. Once you see this, double-click. The formula should fill down automatically.
3.) Highlight this new column of phone numbers and type the following keys (not including the commas): /,h,v,v,enter
4.) Then, copy/paste this entire column over the original column of phone numbers.
Last edited by clabulis; 08-26-2014 at 10:41 PM.
Put the formula in a blank column then drag or copy down to the end of your list of phone numbers. If you want to replace the phone numbers you currently have copy the new phone numbers you have just created and paste special, values over the old ones. You can then format as you want by right clicking on the phone numbers (select them all first) then click on Format Cells, then on the number tab then on custom. In the box directly under Type: put in (000) 000-0000.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks