Converting word rows in to colum

[yogeshwarv]

Hi All,

I need a help from you guys.

I have a file in ms word which has some data in single column and multiple rows. I want this file to convert in excel in single row and multiple columns and new data in next row after space in order to buildup database in excel.

I am enclosed herewith the sample of word document for your kind consideration. Please help!!!!!!! :banghead:

[OTWarrior]

Before I start helping you with this, I would very much suggest you change this word file, as it appears you have confidential data on there.

Unless I am mistaken and it is test data (or even public information), but I would advise you to change your post, as your company may not like you posting such information.

[Tinbendr]

You can do this with regular find/replace. It's a four step process.

Find: ^p^p
Replace: & (or any character not on the page already)
This seperates the records.

Find: ^p
Replace: , (Comma)
This seperates the fields.

Find: &
Replace: ^p
This replaces the placeholder used to seperate the records.

Import into Excel, selecting comma as the Delimiter.

The only problem with this is that the address, city, state, columns won't always match unless you use another placeholder for the missing lines.

[yogeshwarv]

Thanx for your suggestion OT. See this is just a directory and not a confidential data only thing is that this file is in word in row field and I want these data in excel in column & row field. these are more than hundred pages and will take long time to change records in desired format. can this be done with the help of VBA?

Please help

[parttime_guy]

Hi Guz,

I had faced a similar problem some time back.
Iam new to VB, but ... could this be done?

The above doc has some ^p after each address
Can a code pick all lines after each ^p
Copy & Paste in excel in columns (A, B, C...)

Then run a second code from Excel
Transpose the copied columns to rows

Maybe to rows will still have to formatted mannually later.

It's just an thought.

Hope Iam not thinking too much :doh:

Yo!

[parttime_guy]

Hi Guz,


After much research, I tried to solve the problem (n here the solution)


I have attached 2 files (hope this helps)


Step1
Conversion of Word data into formated Text


Step2
Conversion of formated Text into Excel

But.... can the above 2 steps be automated in Vba.

Happy Excelling
Yo!