4/5/11

Geeky Tip: Moving Data from MS Word to Excel

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.

Bookmark and Share

15 comments:

Jennifer Stephens said...

Great post, this is very useful. I had forgotten that you find/replace based on code.

Ayelette Robinson said...

Love it. Thanks, Greg.

SONIA APARECIDA GONÇALVES DOS REIS said...

Obrigada pela dica!
Vou passar a fazer agora, tenho que treinar, pois este sistema para mim e novidade.
Parabéns!!

Anonymous said...

Thanks for writing this down--I know it but now can pass on to staff ;)

Saskia said...

Vielen Dank fuer den Tip!

Kelly Nelson said...

I enjoyed this great tip, thank you!

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.

Thanks again, love 3 Geeks Blog.

Brindes Personalizados said...

Vou passar minha lista de clientes de brindes de word para excell, obrigado pela dica!

Fred Esterly said...

This is something I was looking for long time as I had tried it few days back but I was failed to do it.

smjensen55@yahoo.com said...

What if you only use hard returns between the addresses but use soft returns for each line of the address? This is just a fast trick for formatting purposes/

Tarbender said...

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.

Anonymous said...

Thanks so much for posting this! It was a huge help in organizing things for an upcoming wedding.

The Body Project said...

I have recently started a website, the information you offer on this web site has helped me greatly. Thanks for all of your time & work. Site attribution from your post.

Ray Ray said...

Saved my morning. Thanks much.

Anonymous said...

You just saved my life.

sandra wieland said...

This is great! I like the Excel Paste-Special function to paste values, formulas and formats. For it I can recommend the following site http://www.excel-aid.com/excel-combine-data-pasting-values-formulas-formats-2.html

 

© 2014, All Rights Reserved.