Text to Table without spaces between words
I use Office 2010.
My question applies to Word and Excel.
I use an original pdf file, which converted to Word or Excel generates the tables below.
I want see 4 columns, to be able to remove automatically the space between words, so no copy/paste of words, split the cells individually and later paste the copied text. I do not want to arrange the table manually word by word. In Word I use also Insert-Table-Convert Text to Table.
How can I do that?
P-a-r-t-s----l-i-s-t.docx
Parts-List.xlsx
My question applies to Word and Excel.
I use an original pdf file, which converted to Word or Excel generates the tables below.
I want see 4 columns, to be able to remove automatically the space between words, so no copy/paste of words, split the cells individually and later paste the copied text. I do not want to arrange the table manually word by word. In Word I use also Insert-Table-Convert Text to Table.
How can I do that?
P-a-r-t-s----l-i-s-t.docx
Parts-List.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
For unknown reason, it does not work with my Word file.
I have tried to learn how the wildcards work:
https://support.office.com/en-us/article/find-and-replace-text-or-formatting-in-word-for-mac-ac12f262-e3cd-439a-88a0-f5a59875dcea
Did you try with my Word file to do what you proposed?
For example the colon : at the end of [ ]{10,}: does not work for me.
I have tried without it, but still I do not get what I supposed to get. U understand your suggestions, because I have done something similar in Notepad++, but it does not work for me in Word 2010 with my file.
Could you try with my file your proposed steps?
I have tried to learn how the wildcards work:
https://support.office.com/en-us/article/find-and-replace-text-or-formatting-in-word-for-mac-ac12f262-e3cd-439a-88a0-f5a59875dcea
Did you try with my Word file to do what you proposed?
For example the colon : at the end of [ ]{10,}: does not work for me.
I have tried without it, but still I do not get what I supposed to get. U understand your suggestions, because I have done something similar in Notepad++, but it does not work for me in Word 2010 with my file.
Could you try with my file your proposed steps?
Sorry for the delay. I've been out.
My apologies, too, for the mistype. The colon should not have been there at all.
My apologies, too, for the mistype. The colon should not have been there at all.
ASKER
Still, I it does not work (easy) for me in Word.
How I have done it with Notepad++:
- Save Word as *.txt and open the TXT file in Notepad++
- Select all text in Notepad++ and then with Replace I replaced space " " with semicolon ; in all text, so with Replace All
- With the Replace dialog opened I replaced ;; with ; repeatedly using Replace All until I got only one ; between words. Then I saved the txt file.
- Then I opened the TXT file in Excel with import Data, Delimited with ;
- Then I have done a final adjustment manually on some words.
How I have done it with Notepad++:
- Save Word as *.txt and open the TXT file in Notepad++
- Select all text in Notepad++ and then with Replace I replaced space " " with semicolon ; in all text, so with Replace All
- With the Replace dialog opened I replaced ;; with ; repeatedly using Replace All until I got only one ; between words. Then I saved the txt file.
- Then I opened the TXT file in Excel with import Data, Delimited with ;
- Then I have done a final adjustment manually on some words.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Your approach involves VBA, which I wanted to avoid this time, my files were not very complex, I was just lazy. For long and complex files worth to study your approach, anyway your article is well written.
I would rather prefer GrahamSkan method for not so long files. Anyway I have done it with Notepad++, but I wanted to see how is working also within Word functionality only.
I would rather prefer GrahamSkan method for not so long files. Anyway I have done it with Notepad++, but I wanted to see how is working also within Word functionality only.
ASKER
So, finally I had success with the solution proposed by GrahamSkan, but a bit modified.
I do not know why the {10,} does not work for me.
Maybe GrahamSkan can help me with his proposed {10,}.
This is how I have done it in Word using Replace based on GrahamSkan suggestions:
- Find what: [ ]{2}
Options: Use Wildcards
Replace with: ;
At Find what is a space between parentheses.
- Find what: [;]{2}
Options: Use Wildcards
Replace with: ;
Click Replace All several times until I got 0 replacements
- Find what: [ ]{1}
Options: Use Wildcards
Replace with:
At Find what is a space between parentheses.
At Replace with is nothing, I have deleted any character and even space is not there. In this way one space between 2 characters is deleted.
- Then I corrected by hand some semicolons between some characters in case is needed.
- Select the Table, then go Layout (Word 2010) and then Convert to text and with the option Separate the text with semicolon.
- To delete empty lines:
Find what: ^p^p
Replace with: ^p
Replace All
- With the selected text we go Insert-Table-Convert Text to Table and at Options we choose Separate text at Semicolons.
- Any other corrections that are needed can be done with Undo and Redo on the last operation with Convert Text to Table by correcting the space or semicolon between characters.
- Done.
I do not know why the {10,} does not work for me.
Maybe GrahamSkan can help me with his proposed {10,}.
This is how I have done it in Word using Replace based on GrahamSkan suggestions:
- Find what: [ ]{2}
Options: Use Wildcards
Replace with: ;
At Find what is a space between parentheses.
- Find what: [;]{2}
Options: Use Wildcards
Replace with: ;
Click Replace All several times until I got 0 replacements
- Find what: [ ]{1}
Options: Use Wildcards
Replace with:
At Find what is a space between parentheses.
At Replace with is nothing, I have deleted any character and even space is not there. In this way one space between 2 characters is deleted.
- Then I corrected by hand some semicolons between some characters in case is needed.
- Select the Table, then go Layout (Word 2010) and then Convert to text and with the option Separate the text with semicolon.
- To delete empty lines:
Find what: ^p^p
Replace with: ^p
Replace All
- With the selected text we go Insert-Table-Convert Text to Table and at Options we choose Separate text at Semicolons.
- Any other corrections that are needed can be done with Undo and Redo on the last operation with Convert Text to Table by correcting the space or semicolon between characters.
- Done.
In wildcard finds, the square brackets contain a list of characters to find - only a space in this case. The curly brackets are used to say how many instances of any of those characters are to be found. The first figure (10 here) says 'at least this number'. The second (missing here) figure gives a maximum number, so the whole term translates as ten or more spaces to be found. With a tab character as the replacement text for the multiple space string, the text can be used in the reconversion back to a table.
ASKER
In mean time I have done something similar using Notepad++ and TextFX plugin:
https://www.cathrinewilhelmsen.net/2014/10/15/edit-columns-in-notepad-with-textfx-plugin/
https://www.youtube.com/watch?v=5cQ-1JnsW_g