If you’re like most Excel users, you’re likely self-taught. Nothing wrong with that, I got here in the same fashion. However, from my perspective many users learn enough about Excel to wield it as a hammer when often more nuanced approaches are more appropriate. In this article I’ll give you several ways that you can transform text without retyping a thing.
Let’s say that you’ve inherited a list of data such as shown in Figure 1. You’ve been assigned several tasks:
- Convert the dates in column A into something usable
- Separate the account numbers into column B into three columns
- Eliminate the capital letters in column C.
Figure 1: You can use various techniques in Excel to transform text.
We’ll tackle each of these one at a time. The first thing to know is never, not ever, do you start retyping this data into separate columns. There’s any number of ways to accomplish the above tasks. We could use the DATEVALUE function to tackle column A, and we could use some combination of LEFT/RIGHT/MID to break up column C, but those two columns don’t require formulas. Instead, select column A and then fire up the Text to Columns wizard on the Data tab or menu in any version of Excel:
- In this context it doesn’t matter what you choose on the first step of the wizard, so just click Next.
- Since we’re converting dates, there’s nothing to see on the second tab of the wizard either, so click Next again.
- The third screen of the wizard is the place to be at the moment. Choose Date, and then select the format that your dates are currently in. Don’t make a rookie mistake and choose MDY here, that’s what you want to get to, but at the moment it’s in YMD. Choose that, click OK, and head out for a latte.
Figure 2: Use the Text to Columns wizard to convert the dates in column A into a usable format.
Oh, you’re back for more? Let’s do this then. Click on column B, and then crank up the Text to Columns wizard again, and then:
- Take a moment to choose Delimited on the first tab of the wizard. Yeah, you can argue with me that this data is fixed-width, but there’s a method to my madness here, which I’ll reveal after you click Next.
- Tag that checkbox labeled Other, and then type a dash in the space. Keep in mind that even though it looks like you could type a couple of letters here, you can only do one, so click Next and move on.
- If you get in a rush and press the Enter key here you’ll inadvertently overwrite the data in column C, as shown by the error prompt in Figure 3. So, slow down there and change the Destination to cell D1, and then you can click Finish.
- Voila, as shown in Figure 4 you’ve done gone and busted up that account number. Time for a cruller to accompany the latte.
Figure 3: If you press the Enter key or click Finish before changing the Destination location, you will receive this error prompt.
Figure 4: Use the Text to Columns wizard to separate the account numbers into three columns.You say I forgot column C? No, not at all. Now if we were using say Word, Outlook, or even PowerPoint we could select a block of text and then press Shift-F3 a time or two to toggle the case of those words. And there’s nothing stopping you from grabbing that column, blasting it into a blank Word document, using Shift-F3, and then putting the data back. Feels like a lot of trouble? It could yet be the path of least resistance once I show you how Excel rolls.
In cell G2 type this formula:
=PROPER(C2)
Click back in cell G2 and then double-click that little notch in the right-hand corner known as the Fill Handle, which will copy the formula down the rest of the column—but you knew that already. Select column G, press Ctrl-V, right-click on cell G1, and choose Values.
Notice in cells F3 some of the quirks that the Proper function exhibits: the next letter after an apostrophe gets capitalized. Not a bad deal when your name is O’Callahan, but rather annoying when the text is something like Bob’s TV Service. Speaking thereof, no matter if you’re using Word or Excel, when transforming text either Shift-F3 or the PROPER function is only going to get you about 95% there. You’ll need to scan the text and manually fix the faux pas that appear. Of course, one shortcut is to press Ctrl-F, search ‘S and replace it with ‘s, as shown in Figure 5. Just don’t hit Replace All unless you’re sure there’s not an O’Shaunnessey on the list.
Figure 5: Use the PROPER function to get rid of excessive capital letters. Use the Find and Replace feature to fix the ‘S that occur from the PROPER function.About the author:
David H. Ringstrom, CPA, heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, teaches webcasts for CPE Link, and writes freelance articles on Excel for AccountingWEB, Going Concern, et.al.
Over the last couple months, GC has been profiling various accounting-related credentials. 