Moving Text from Word to Excel (and having it set into 2 columns)

[Jim D.]

I have a Word Document that has the follow format. . .

word , word
word , word
word , word
word , word

.. . . It’s basically a two page list of 2 words separated by a comma per
line. But the spacing between the words is sporadic, and likewise the comma
doesn’t have any defined (fixed) position between the words.

I’d like to export this list into Excel so that Excel will read it as 1 row
of 2 columns . Do I need to first standardize the spacing between the words
and comma, or is there a better way to do this?

I’m not sure what the best approach should be. I have never created a CSV
doc in Word before, and for that matter I can't ever remembering a time where
I imported from Word into Excel either. So, I would love to get any feedback
on this situation.


Take Care, and Thanks,

Jim

[ for those curious I got this of of the internet. the comma's used to be
dashes but I changed them over to comma's thinking that would be the first
step in the right direction. Obviously I don't know what the next steps are.
]

 

[Jim D.]

I've managed to stumble upon a solution. By using Text to Columns.

This works nicely but now my 2nd column entry's have an empty space before
each word.

Anybody know How I can get rid of the empty space before the word?


Thanks,

Jim

 

[Fred Smith]

One solution:
In Text-to-Columns, define the space as a delimiter, and check off: Treat
consecutive delimiters as one.

Fred

 

[Ms-Exl-Learner]

You can remove the unnecessary spacess by using Wildcards in Ms-Word.

For your below query just open your word document and give Cntrl+H (to get
the Replace Dialog Box. Now click the More button or (M OR ALT+M) and check
the Use Wildcards.

In find what box copy and paste this [ ]{1,100},
In Replace with box copy and paste this ,

Now click Replace All or Alt+A.

Now your text is converted From
word , word
word , word
word , word
word , word

To
word, word
word, word
word, word
word, word

Once again
In find what box copy and paste this ,[ ]{1,100}
In Replace with box copy and paste this ^t

Again Click the Replace all button.
word word
word word
word word
word word

Now your word file data structure is converted to excel compatibiltiy now
you can copy and paste the data in Excel.


For more details about Word Wildcard and its usages go through the below
websites:-
http://www.gmayor.com/replace_using_wildcards.htm
http://word.mvps.org/faqs/general/usingwildcards.htm

Remember to Click Yes, if this post helps!

 

[Ms-Exl-Learner]

If your data is starting from 1st row use the below forumla:-
Paste this formula in C1 cell
=TRIM(B1)
Copy and paste the C1 cell and paste it to the remaining cells of C Column.

If your data is starting from 2nd row use the below forumla:-
Paste this formula in C2 cell
=TRIM(B2)
Copy and paste the C2 cell and paste it to the remaining cells of C Column.

Remember to Click Yes, if this post helps!

 

[Paul]

Hi Jim, you might try the following macro. Change the word doc an
text file paths/names as necessary. Also, you'll first need to turn o
the reference to Microsoft Word 12.0 Object Library in the VB Editor o
Excel (Tools -> References -> scroll down and select that library an
click OK).



Code:
--------------------


Sub wrd2exl()
Dim wdApp As Word.Application, wdDoc As Word.Document

On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0

Set wdDoc = wdApp.Documents.Open("C:\temp\Word1.docx")
wdDoc.SaveAs Filename:="C:\temp\Word1.txt", _
FileFormat:=wdFormatText, LockComments:=False, Password:="", _
AddToRecentFiles:=True, WritePassword:="", _
ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, SaveFormsData:=False, _
SaveAsAOCELetter:=False, Encoding:=1252, InsertLineBreaks:=False, _
AllowSubstitutions:=False, LineEnding:=wdCRLF

wdDoc.Close False
wdApp.Quit
Set wdDoc = Nothing
Set wdApp = Nothing

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\temp\Word1.txt", Destination:=Range("$A$1"))
.Name = "Word1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub