grindmygears
asked on
Need to replace characters in excel (replace and remove some characters)
Hello,
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.
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.
Last Comment
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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 think points should goto jmiller.
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
ASKER
Thank you both
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
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!!