I’m going to share with you two little tricks that I learned many, many years ago that will hopefully save you a lot of time when dealing with large amounts of data. Both of the tips are pretty simple, and perhaps you already know them. However, I’ve found that most of the folks I run across don’t, and they spend an enormous amount of time manually entering data when they don’t need to.
Both of these tricks involve taking text from MS Word and transferring it into MS Excel. If you’re like me, you tend to do this sort of thing all the time (because for some reason, Partners really like to see the information in a spreadsheet.)
Tip #1 – Converting Carriage Returns into Tabs
The most basic elements of transferring data from Word to Excel is that the columns are essentially separated by tabs, and the rows are separated by carriage returns. The big trick is using the “Find and Replace” option in Word to help you clean up the data automatically, rather than going through line by line and doing it manually. So, let’s take a simple example of a single address:
1111 First St. Apt. 4A Houston, TX 77002
We could manually remove the carriage return at the end of each line, but why not just let Word’s “Find and Replace” function do that for us??
With the address above typed into MS Word, simple open the “Find and Replace” option by typing CTRL+H (or locate it on your toolbar/ribbon). You may also want to turn on the “Show/Hide ¶” feature (which will show the carriage returns and tabs in the document) by typing CRTL+*, or finding the button on your toolbar that has a paragraph symbol (¶) in it.
In the “Find what:” box type in ^p
In the “Replace with:” box type in ^t
Hit “Replace All”
The “^p” is Word’s code for the carriage return, and the “^t” is the code for a tab. So, all we’re doing is removing the carriage return and replacing it with a tab.
Once you’ve replaced the carriage returns with tabs, the text should now look like this:
Copy that line and go to the cell in Excel that you want to paste it… but don’t paste it yet… there’s still one more trick to do. Because I don’t like to bring in all the formatting from MS Word into my Excel spreadsheet, I use the “Paste Special” -> “Text” or “Unicode Text” feature. This will strip out all the formatting that a regular paste function would bring in. So, if you “right-click” -> “Paste Special” -> “Text”, you should get something that looks like this:
That works great if you only have one address, however, you’ll probably have an entire list of addresses that you want to move from Word to Excel. Here’s a way to to that.
Tip #2 – Moving Multiple Address from Word to Excel
I’m going to make one big assumption here, and that is that each of the addresses you have in Word is separated by two carriage returns. With that in mind, what we need to do is add a step at the beginning and end of the procedures we did with the single address so that we can move the next address to the next row in Excel.
Here are our addresses:
1111 First St. Apt. 4A Houston, TX 77002 1111 First St. Apt. 4B Houston, TX 77002 1111 First St. Apt. 4C Houston, TX 77002
Step one is to remove the double paragraphs and replace them with a “line return.” The reason is that we still need to keep the data on separate lines, and this will allow us to do so without messing up the ability to put the tabs in the right place. Now, for most of you, the carriage return and the line return look pretty much the same on MS Word, but trust me, Word knows the difference and we can take advantage of that.
Open up the “Find and Replace” function again.
In the “Find what:” box type in ^p^p
In the “Replace with:” box type in ^l
Hit “Replace All”
Just as before, the “^p” means carriage return, but the “^l” (that’s a lower-case L) is the code for a line return.
Now we repeat our previous “Find and Replace” function to remove the remaining carriage returns and convert them to tabs.
In the “Find what:” box type in ^p
In the “Replace with:” box type in ^t
Hit “Replace All”
Now, you don’t necessarily have to do this next step, but I like to do it just to keep the data clean and consistant. I like to convert the line returns back to carriage returns before I paste it over to Excel.
In the “Find what:” box type in ^l
In the “Replace with:” box type in ^p
Hit “Replace All”
You can now copy over the information from Word and do your “right-click” -> “Paste Special” -> “Text” in Excel and end up with something that looks like this:
Just as with most “tips and tricks” there are always little things that can pop up that throws the steps off a little, but most of the time you can work your way through those little hick-ups.
Hopefully, these two little tricks will help you out in the future when you have pages and pages of data that you need to move from Word to Excel. Instead of send that document over to your Secretarial Services to have them manually type it in, use this and save everyone the time and effort.