In this post, we are thrilled to share with you 2 ways to copy texts from Word to Excel with each word in a separate cell.
If you are a linguist, you probably need to do the word frequency count a lot. Then our topic today shall be a great help. The 2 methods bellow will enable you to paste texts from a document to excel sheet and list each word in a column cell.
Method 1: Make Use of the “Find and Replace” Option
- To begin with, make a copy of the target document. This is extremely important and we will conduct all operations in the copy file to prevent from data loss.
- Secondly, select all target texts and apply them with “Normal” style. To do this, press “Alt+ Ctrl+ Shift+ S” to open the “Styles” window. Then find and click “Normal” style.
- Thirdly, press “Ctrl+ H” to trigger the “Find and Replace” box.
- Put cursor at the “Find what” text box and enter a space there.
- Next place cursor at “Replace with” text box and enter “^p” there.
- Then click “Replace All”.
- Click “No” in the next box popping up.
- Now you can see each word is in one line. But there is also a blank line as follows:
So to remove it, you need to invoke the “Find and Replace” box again.
- This time enter “^p^p” in “Find what” text box and “^p” in “Replace with” box.
- Click “Replace All”.
- Now press “Ctrl+ C” to copy the word list.
- Open Excel and press “Ctrl+ V” to paste the list.
Method 2: Use VBA Codes to Reduce Workload
- Similarly, make a copy first and select all target texts.
- Next press “Alt+ F11” to open VBA editor.
- Then click “Normal” and “Insert” in order.
- And choose “Module” to create a new one.
- Double click the module to open it and paste the following codes:
Sub CopySelectParagraphWordByWordToExcel() If Selection.Range <> "" Then Selection.Style = ActiveDocument.Styles("Normal") Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = " " .Replacement.Text = "^p" .Forward = True .Wrap = wdFindStop .Format = False .MatchCase = False .MatchWholeWord = False .MatchByte = True .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With Selection.Find.Execute Replace:=wdReplaceAll Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = "^p^p" .Replacement.Text = "^p" .Forward = True .Wrap = wdFindStop .Format = False .MatchCase = False .MatchWholeWord = False .MatchByte = True .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With Selection.Find.Execute Replace:=wdReplaceAll Selection.Copy MsgBox ("Now you can just open a Excel worksheet to paste the text") Else MsgBox ("There is no selected text ") End If End Sub
- Then click “Run” button.
- Next there will be a message box indicating what you need to do next.
- Open Excel and press “Ctrl+ V” to paste the texts there. The effect is as same as that in method 1.
Deal with Word Document Errors
Have you ever fall the victim to Word corruption? If so, you will understand how desperate one might get. Therefore, it becomes more important than ever to get a qualified repairing tool to fix your broken document.
Vera Chen is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including recover xlsx and pdf repair software products. For more information visit www.datanumen.com