fstinc
asked on
How do i transfer this to excel
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
rspahitz
Just curious if this is a different question from this: https://www.experts-exchange.com/questions/26853326/Put-data-on-excel.html
@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
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
@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
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
ASKER
unfortunately copying and pasting was the only way.
@fstinc - my spreadsheet solution didn't work for you? No feedback/problems that I could resolve????
Dave
Dave
ASKER
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.
Thank you so much for your efforts - greatly appreciate your help. next time i won't wait till the last minute.
Ok - no problem. I was just intrigued at how you awarded the points.
Cheers,
Dave
Cheers,
Dave
ASKER
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.
thanks again.
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
No worries,
Dave