Avatar of grindmygears
grindmygears asked on

Need to replace characters in excel (replace and remove some characters)


I have a 5000 line ling excel document. In column "AG" I have a long list of phone numbers. I need to remove the "-" from the numbers. E.g. 555-555-5555 needs to become 5555555555 all down the column. Similarly, I have column "J" which has birth dates. I need to remove the / from the birthdates and replace them with dashes. E.g. 5/5/1980 needs to be 5-5-1980.

Is there a way to do this without going through all 5000 lines? Please let me know if there is!

Thank you so much.
Microsoft Legacy OSMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment

8/22/2022 - Mon
Berkson Wein

You could get tricky and do a regex find but replace, but that's more complicated that what you need based on your description.

You'll do 2 find and replaces.  First select all of column AG.  Copy that to a new sheet.  On that new sheet, hit ctrl-f for find.  In the find what field enter a dash, in the replace with field don't enter anything.   Click replace all.  Copy that data back to the original sheet.

The repeat this process with AJ, finding - and replacing with /

Hope this helps!!
Jerry Miller

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Berkson Wein

caution on jmiller's sollution though - that'll replace any dash in the entire sheet, so if you have dashes in any other column, they'll be removed too.  That's why my solution moves the data temporarily to another sheet first.
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Berkson Wein

Harry is ABSOLUTELY correct.  MY followup comment jmiller's is WRONG provided that the column is selected first.  

I think points should goto jmiller.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Jerry Miller

weinberk, I don't mind splitting the points if that is what grindmygears would want to do. Or give them to you since you posted a correct answer first. I must have been typing mine while you posted since I was less than a minute behind you. I generally try to do a page refresh before hitting submit to avoid that, but I was in a hurry this morning. :D

Thank you both