# Macro to import text from Word to Excel



## pwyf (Mar 5, 2009)

Hi everyone,

I am working on several Word documents which look like so.

Here is how each block of text is arranged:

Numbers tab Numbers Paragraph
Text Paragraph
Text Paragraph
Paragraph

I would like to import this data into Excel to make it look like this. 

First column: first series of numbers
Second column: second series of numbers
Third column: First line of text(alt+return)Second line of text

I hope all of this is clear.

I have tried creating a macro on my own, but I don't know Visual Basic, so I have failed miserably.

I need to do this on 40 documents which contain up to 100 blocks of text.

Any help would be appreciated!

Thanks a lot.


----------



## macropod (Apr 11, 2008)

Hi pwyf

Does the 1st paragraph everhave more than the two times and a single tab separating them?
Do the other paragraphs have tabs or commas in them?


----------



## pwyf (Mar 5, 2009)

Hi macropod,

The tab only appears in the lines containing numbers.
On the other hand, there are often commas in the lines of text.


Hope that helps!

pwyf


----------



## macropod (Apr 11, 2008)

Hi pwyf,

Here's some code that takes the active document and converts its contents to a tab-delimited format suitable for exporting to Excel:

```
Sub MakeTabDelimitedText()
Dim EndRng As Range
With ActiveDocument
  .Paragraphs(1).Range.Text = Replace(.Paragraphs(1).Range.Text, vbCr, vbTab)
  With .Content.Find
  .ClearFormatting
  .Replacement.ClearFormatting
    .Text = "^p"
    .Replacement.Text = Chr(10)
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    .Execute Replace:=wdReplaceAll
  End With
  Set EndRng = .Range.Characters.Last
  With EndRng
    .MoveStart wdCharacter, -1
    .Delete
  End With
End With
End Sub
```
There is plenty of code about for looping through multiple files in a folder and also for exporting the content of such files to an Excel workbook - I've posted some in various forums myself. A web search should locate some suitable examples which, with the code I've posted above, you should all you'll need to build a suitable routine.


----------



## pwyf (Mar 5, 2009)

Thanks a lot, macropod!


----------

