Data manipulation seems to be an almost daily occurrence for me. Well, maybe not daily, but I do it so often that I don’t even think about it anymore.
IT is supposed to be this always-working, man-behind-the-curtain magical thing, and while it works – it’s awesome. When it doesn’t work, however, we’re stuck either working around it, or fixing it.
The other day someone sent me an email address list in the body of an email. Well, it never came across, so I had the person put it into a Word Document and send me that.
What I didn’t know, was that the Word Document now contained a table. Oh well – copy and paste that into an Excel sheet, and it did just fine.
Now, I had an email address in the left hand column, and then I had a full name surrounded by (parentheses). Why??
OK, so now I have
firstname.lastname@example.org (Mickey Mouse) in my Excel file
First I need to remove the parentheses. How would we go about doing that?
Well, I could have used Text-to-Column but in this case I just used Find and Replace. (Ctrl+H) is the keyboard shortcut for that.
Find and Replace
I had to look for the actual ribbon controls, which are under the Home tab, in the Editing section. Click Find & Select, then Replace.
Enter the ( character in the Find What, and leave Replace With blank so that it will just remove the character from your text. Click “Replace All” to remove all the parentheses. Then do Find & Select and Replace again, and enter the ) character in the Find What.
Text to Column
A lot of the time, I need to split the full name into two columns, with first and last name. One of the easiest ways I know to do that is to use the Text to Column feature, which you find under the Data tab.
On the first Wizard screen, choose Delimited. On the second screen, add a checkbox for “Space”.
Note how the preview splits out Mickey from Mouse? That’s what you want.
Now this is not foolproof, because if there are middle names, or a surname that contains a space, you need to clean up the data. You could do that using the Filter feature if you have a large list, or you can eyeball it if the list is fairly small.
In looking up this blog, I realized I haven’t done any posts on Filters yet. Shame on me! I’ll spend some time in the next few posts on what to do with Filters. They are incredibly powerful and I use them often.