Hi - I have a list of about 3,000 phone numbers; when I export from our software program into Excel there are ( ) around the area code and a - in between the parts of the numbers
ie. (555) 555-5555
I need the parenthesis and the hyphen gone, plus no extra spaces, just one long number consisting of area code and phone all together.
Is this doable??
thanks!!
Last edited by brlcarol99; 07-07-2014 at 02:51 PM.
Try..in a separate column
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"-","")," ","")
Life's a spreadsheet, Excel!
Say thanks, Click *
If you always have the full 10 digit #
=MID(A1,2,3)&MID(A1,7,3)&RIGHT(A1,4)
In the column next to the phone number column?
find and replace all not an option?
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
ok that worked - i can just delete the old column then. Thank you!!!
Ok just spoke to the company we are sending this sheet to, we can't have hidden columns - any suggestions?? I had added in the substitution formula and then just hid the column
Try this...
A2 = (555) 555-5555
Enter this formula in B2 and copy down as needed:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),") ",""),"-","")
Result will be 5555555555
Select the entire range of formulas
Right click>Copy
Right click>Paste Special>Values
Inspect the results to make sure they are what you want
Delete column A
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
You're my hero - that worked!! thank you!
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks