I need to do an upload and the format of our phone numbers needs to be 999-999-9999
Right now they are all
(999)999-9999
How can I change the phone numbers to look like 999-999-9999
on my spreadsheet I am working on the the phone numbers start on AV595 and go to AV795
Also some of the phone numbers are missing the area code the just look like 999-9999 how can I add in an area code to look like 999-999-9999
this starts on the spreadsheet at AV6 and goes to AV594
Let me know if you need more info.
Thank you!
In a free cell in the same row, enter
=IF(ISNUMBER(SEARCH("(",AV6)),REPLACE(REPLACE(AV6,1,1,""),4,1,"-"),"999-"&AV6)
replacing 999 with actual area code and assumes same area code for all.
and copy all the way down to row 795.
Then copy this range containing the formula and go to AV6 and then right-click and select PAste Special and select Values.. click Finish,
Then delete the column you added with formulas.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks