A law blog addressing the foci of 3 intrepid law geeks, specializing in their respective fields of knowledge management, internet marketing and library sciences, melding together to form the Dynamic Trio.

Pages

4/5/11

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.

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.

But, I would like to correct the example you used. I manage the CRM Database at our firm, and one thing I am very strict about is that the apt, suite, etc, should never be below the street address. Always above or at the end. The post office machines read bottom up and need to know the street before the address. Plus having the apt at the end of the street is more effective use of space on a mailing label.

Of course the mail will still make it to its destination, and its not the end of the world. But I learned this interesting tid bit from a colleage whose husband woked at a post office and like to share it whenever possible.

Holy snap that first tip is going to save me so much time it is crazy! I always start creating tables in Word -- don't ask why, I am weird like this -- but once I realize I need to start moving data into a spreadsheet it becomes a painstaking mission to cut and paste everything. I do a ton of keyword research for my goals website. This tip is awesome. Thanks a hundred times over.

I just tried using this method and I'm sure it should work but it doesnt seem to work for my list. I was given a 45 page list in word of addresses... Some have 3 lines some have 4 lines of addresses but its not a big deal I can fix those once Im in excel.. I just need to be able to get each address into its own row in excel. I tried using the find and replace but it says that there are no carriage returns or only 1 to replace... soo any suggestions on a quick way to do this? 45 pages is a lot of addresses to manually move to excel.. ahhh help!