Convert Resource Table from Word to Excel

Since all the materials in Lowe’s Resource Alignment is copyrighted, instead of sharing my table I created for automating the resource alignment Dewey numbers and years I have put together this guide to show you how to convert the digital Word document to a more useful Excel document. This is just how I did it, there are probably things that could be done differently to speed it up. If you have any suggestions please contact me and let me know!

Step 1 Copy the table over

Open up the document in Word and select the table.

Before we paste the document in, we need to tell Excel that we are pasting in text, so it doesn’t try to auto-guess what we are pasting in. If you don’t do this it will covert some of the useful columns to dates, which as I talk about later isn’t a huge deal as you can create the useful range using formulas. Select the entire document (Ctrl+A) and change the format to Text.

Click in cell A1 again and while on the Home tab, click the drop-down arrow under Paste and choose the second option (Match Destination Format). This will force the content from the Word that is pasted in to use whatever format that the cells already have – in this case text.

Step 2 Make it into one LONG list.

Everything should look very similar to what you see in the Word document.  Now you will need to go and copy / paste the data into one long list. I find this easiest to make a new sheet, and select the columns I want and paste them in. Go through and put all of the information in one long list. I kept everything “as is” from the document and went back and made corrections afterwards. Don’t forget to save along the way!

Step 3 Making Corrections

Text Overflow / Double Lines – One of the most common correction you will make is fixing double lines. Since this was a direct paste, if the text was in two cells in Word, it will be in two cells in Excel.  Just copy the text from the second cell into the first. These are easy to spot as they don’t have year data with them.

Before: 

After:

Multiple Dewey Numbers

For the script to work right, every Dewey number has to be on its own line. So in this case, when there is a range, it has to be separated out.

000-1 General Works becomes

000 General Works
001 General Works

Each on their own line with the same keep/useful/discard information

Changing the years

The script is looking at the year the book was published. If the book was published in 1950 and the chart shows 03 (suppose to be 2003) well that would be a keep because 1950 > 3 – even though it should be a discard.

We will need to change the years and remove all the less than/greater than symbols.

To change the years hit Ctrl+H in Excel to bring up the Replace menu. The first that you want to do are replace > with nothing and replace < with nothing.  Remove all the less than / greater than symbols this way.

Open the Replacement menu again (Ctrl+H) and click on Options. Start with one of the years 90 and replace it with 1990. Make sure to match entire cell content, so you don’t change 05 in General Serial Publications and change the Dewey number from 050 to 02005.

You will need to change all the years from 90 to 1990, 91 to 1991…. all the way to 15 to 2015 – potentially more if the years go farther back than 1990. This is something else that is probably scriptable but I only had to go through 25 replaces, so it was just faster for me to do the replaces.

Now your chart should look better with all the years instead of just the last two digits – and give the script the numbers it is looking for.

I just deleted the useful year range and replaced them with a formula based off Keep -1 for Useful max and Discard +1 for Useful Min.  While you really don’t need the useful years from the chart, I like to have them just to double check. The script itself is using Keep-1 and Discard+1.

You will note that your formulas display as text. This is because those columns are still considered text and not formulas (we changed everything to text before). You will need to select those columns and change them back to “General” on the cell format.  I had to click in the cell and press enter again to get them to process the formula.

Scroll through the list and look for any glaring errors such as rows without Dewey numbers or problems above that were missed.

Step 4 Separate out the Dewey # from the Category

Now when I originally made the document, the Dewey # was not separated from the category. You can split it by using this formula which splits it at the first space. I added a new column in Column A and had the Dewey Category with the number / text in Column B. In Column A I added the following formula that would look at the text in column B and split it at the first space that separates the number from the text.

=LEFT(B2,FIND(" ",B2)-1)

With the function:

Function computed:

Step 5 Updating the Excel Document to the Current Year

My document that I pulled data from was published in 2015. Since it is now 2017 (at the time of this writing) I will need to update the years by 2 years.  What I did was take the Keep / Useful / Discard years that I had gotten from the appendix and copied them to N, O, P, Q as seen in the image below. You don’t have to copy the Dewey information but I did anyways.

 

 

I put the publication year in I1 and the Current Year in I2. I3 has the formula =I2-I1 just so it will auto update when I change the current year.

Now in C1 you will need to put in the formula =N1+$I$3

N1 is the copied data from C1

What this does is it will take the value in N1 and add it to I3. I use the $I$3 because I always want the value of I3. If you take the formula and copy it using the fill handle, the formula that references I3 will change unless you use the $ signs. Now you can use the fill handle to copy the formula to the other cells and it will copy correctly / update the years.

Step 6 Manually Spot-Check the List

This is the least fun part. Going through the list and making sure all the numbers lined up correctly. Honestly I just asked one of my associates to do half and I did half. Check every 10 or so rows and make sure it lines up with what the Appendix says. You can take your chances on if it all lined up and not do it, but I felt better about double checking.