Solved

How do i transfer this to excel

Posted on 2011-02-28
10
160 Views
Last Modified: 2012-05-11
I realize this data is not perfectly consistent, but is there any way i can transfer to excel? even i have to clean it up? dummy data attached. the first row can be removed, i know it's funky. dummy-data.doc

User DLMille had helped with something similar before. Post ID: 26681759.  Just wondering if something like that would work for this, but i can't remember how to do it.
0
Comment
Question by:fstinc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 23

Accepted Solution

by:
Michael74 earned 500 total points
ID: 35002984
Since the data is in a table for can just copy and paste the data. I tried this with the dummy data and it worked but it will required quite a bit of cleaning up

Michael
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35003107
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35003599
@fstinc - I've been pretty busy today, but did a quick review and the result is this.

Opens the word document, saves it as a TXT file (given there are table cells that have multiple addresses, importing table elements didn't prove to be a simple exercise).  Then, it imports the TXT file into Excel.  It appears to be one column, so that's all I dealt with.

Sheet1 has the import data, Sheet2 will have the output data

Then, it processes each address into a columnar table data dump - given the addresses are not that predictable (and I'm a bit pressed for time), I decided to continue to dump into the next column until a blank line informed me (hopefully correctly) that the address has changed.

Note, with this implementation, there'll be a need for some manual cleanup.

See attached file with outputs from your dummy-data.doc in Sheet2.  On quick examination, it appeared there could be 68 formatting errors out of > 6000, so perhaps this gets you to the next step.

Cheers,

Dave
WordtoExcel-r3.xlsm
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

Expert Comment

by:dlmille
ID: 35003607
@fstinc - one more thing and I've got to run - didn't have a chance to make changes to avoid this, but to run the macro, you'll first have to go to VBA Project Editory (Developer's ribbon, View Code, or Tools, Macros).  Go to Tools->References and add the Microsoft Word Object Library to your list of references (the large list will be in alphabetical order).

If this is headed in the direction you want to go, let me know and feel free to ask questions/provide info on format for fine tuning.

Dave
0
 

Author Closing Comment

by:fstinc
ID: 35023604
unfortunately copying and pasting was the only way.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35024145
@fstinc - my spreadsheet solution didn't work for you?  No feedback/problems that I could resolve????

Dave
0
 

Author Comment

by:fstinc
ID: 35024290
it didn't work Dave. data was really too messy.  it took me HOURS to do it manually which is what initially i was trying to avoid.

Thank you so much for your efforts - greatly appreciate your help.  next time i won't wait till the last minute.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35028234
Ok - no problem.  I was just intrigued at how you awarded the points.

Cheers,

Dave
0
 

Author Comment

by:fstinc
ID: 35030081
sorry, I apologize. To be honest i don't really know how to use EE effectively. I should really think about how I manage it. Again, my apologies and I will review all the "rules" but mainly use common sense ;o)

thanks again.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35031148
no rule issues, I just thought what I provided would have earned something.   At least there's a macro that you can use to import the data rather than manual copy/paste, etc....

No worries,

Dave
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question