How to use VBA read entries from a Wrod file into Excel

[Robin]

I really really need help on this and andy input would be highly
appreciated:
I am doing a research project and recently got a data file. However,
this data file is in word format and needs to be transformed into excel
for analysis. Each entry of the data file is some basis information
about a company and the file is laid out as follows:

ABC Company
123 Some Rd., East City, CA 76543, 223-345-4567
A local leader in electronics manufacturing

DEF Company
456 Good St., West City, CA 90987, 209-876-7654
Had a new CEO this year and is planning to open an office in LA

GHI Company
789 Lucky St., South City, CA 67887, 321-876-0987
Going to close in 3 months
.......

Btw, all company names are in bold letters.

I believe that there should be a way to work this out with VBA but I am
not familiar with it. I would not mind spending some time to learn it
but it would really help if somebody could give me some leads, like a
template or something.

Many many thanks!

Robin

 

[Dave Peterson]

If you're familiar with excel, why not just do a copy from Word and paste into a
(temporary) worksheet.

Then you can use all your excel techniques to transform the data.

(If it's a one time thing, I would think that would be the simplest approach.)

 

[Robin]

Dave,

Could you give me more ideas about that? I did try that but the problem
is that each line in the word file then becomes a single row and I do
not know how to move them into columns - I mean, like normally you
would expect of a record with several variables. If you know how to do
that efficiently, I would really appreciate!

Thanks a lot!

Robin

 

[Dave Peterson]

Once you have the data in a single column (multiple rows), there's lots of ways
to transpose it.

But you'll have to give more info.

If the data is always 3 rows then a blank row and it starts in A1, you could
use:

In B1:
=OFFSET($A$1,4*(ROW()-1)+COLUMN()-2,0)

Drag across to D1
and then drag down until you run out of data.

And then select B
edit|copy
edit|paste special values
delete column A


If the data isn't like that, you might be able to do it with formulas or a
macro--but it would surely depend on what it looks like and if there's enough
info to determine what goes where.

 

[Robin]

Dave,

Thank you so much for your help! I will try this and see how it goes.

Robin