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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Berkson WeinTech FreelancerCommented:
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 MillerCommented:
Highlight the column, CTRL+F to display the Find menu, click the Replace tab. Type the character that you want to replace ("-" in your first example). Don't type anything in the replace with textbox and choose REPLACE ALL.
Berkson WeinTech FreelancerCommented:
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.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Harry LeeCommented:
All dash on sheet being remove problem is not valid.

All you need to do is select Column AG, Ctrl-H, then Type - in Find What, then leave the Replace With blank and click Replace All. It will apply the replace only to the column if the column is selected.

Then do the same for Column J. Select it first before doing the Replace All.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Berkson WeinTech FreelancerCommented:
Harry is ABSOLUTELY correct.  MY followup comment jmiller's is WRONG provided that the column is selected first.  

I think points should goto jmiller.
Jerry MillerCommented:
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
grindmygearsAuthor Commented:
Thank you both
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Legacy OS

From novice to tech pro — start learning today.