I have a bizarre problem that I cannot figure out for the life of me. I'm trying to import a space delimited Word doc into Excel, but Excel is not recognizing the " symbol as a text qualifier. For example, "Dallas TX" is importing into two columns instead of one. I've tried using a Mac vs. Windows and saving the document with different encoding options, but nothing seems to work. When I used notepad though, it imported correctly! Any help would be greatly appreciated.
Welcome to the forum:
Instead of quotes, use commas or tabs as separators. If your work is set up like I think it is, in Word use Find And Replace " " with , for comma separated values or Find and Replace " " with ^t for tab separated values.
If that isn't what your problem is, please upload a sample of your source document.
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Thank you for the welcome and reply!
I don't think converting the document to tab or comma delimited will work well. I'm trying to import recipes into Excel, and I'd like the quantity, measurement, and ingredient to be in separate columns. But I'm running into trouble when the quantity isn't a whole number. Same goes for the ingredient if it's more than one word. So, for the line "5 1/2 cups flour" the 5, 1/2, cups, and flour are all going into separate columns. I read that text qualifiers would fix this as long as I put quotes around the 5 1/2. But Excel is ignoring them and still putting everything in separate columns. Oddly enough, Excel imports it correctly if I use Notepad instead of Word. I've attached screenshots of both cases so you can see exactly what's going on.
A picture in this case doesn't do much good. Please upload a representative file of your source data untouched.
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Alright, hopefully this works! I've attached a Sample Workbook with before and after sheets. The before sheet I've imported from Word and the after sheet I've imported from Notepad. The text in both the Word and Notepad documents looks like:
5 cups "white flour"
"1 1/2" tsp salt
For some reason Excel imports Word docs differently from Notepad even though they're both saved as txt files. I really prefer using Word and hope I can find a solution.
Thank you for your time and help!
It would probably help more if we could see what your word data look slike?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
What does your Word file look like. Word has far more powerful text handling than Excel ever dreamed of. Please attach your Word file.
Here's the word document I used for importing. I was thinking that enclosing two words with quotes would cause Excel to import them together into one column
Here is what your source file should look like. I have shown 1 example of TAB delimited and 1 example of COMMA delimited format. You would then import into Excel using the Data Tab get external data from text. Make sure that Windows Ansi is identified as the source for the data or you will get strange results for the fractions.
Result
Last edited by newdoverman; 06-01-2016 at 09:02 AM.
Thank you so much newdoverman! The doc imports great when comma delimited. I thought I needed to use space delimited, but Word's replace tool makes it really easy to substitute commas for all the spaces. Thanks again for your help!
You're welcome.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks