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

Posted on 2013-09-04
Medium Priority
Last Modified: 2013-09-05

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.
Question by:grindmygears
LVL 15

Expert Comment

by:Berkson Wein
ID: 39463632
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!!
LVL 18

Assisted Solution

by:Jerry Miller
Jerry Miller earned 1000 total points
ID: 39463637
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.
LVL 15

Expert Comment

by:Berkson Wein
ID: 39463703
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

LVL 12

Accepted Solution

Harry Lee earned 1000 total points
ID: 39464471
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.
LVL 15

Expert Comment

by:Berkson Wein
ID: 39464586
Harry is ABSOLUTELY correct.  MY followup comment jmiller's is WRONG provided that the column is selected first.  

I think points should goto jmiller.
LVL 18

Expert Comment

by:Jerry Miller
ID: 39465206
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

Author Closing Comment

ID: 39467011
Thank you both

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
This tutorial is about creating a new Microsoft Online User Profile account along with how to transfer your files and settings. You may be faced with this situation if your existing user profile has become corrupted.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question