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

[macropod]

Hi yogeshwarv,

Are these data represenative of what you're trying to process? If so, there are some issues that need to be addressed.

Firstly, the layouts are inconsistent. For example, 4th & 5th lines of the first record comprise entries that span 2 lines each in the 3rd record.

Secondly, the 'contact' fields for the records aren't consistent. You seem to have a mix of 'Tel', 'Off' (with 2 numbers on the one line), 'Res', 'Fax', Mob' and 'e-mail', but these don't all apear against each record. Plus the phone numbers are in different formats.

So simply converting the data to a comma-delimited layout for importing into Excel is unlikely to be sufficient. If you do that without massaging the data, the entries across the columns will be quite inconsitent. All this could be dealt with in code PROVIDED one can determine a clear set of rules as to how the inconsistencies can be identified and addressed.

This is not a trivial undertaking.

[yogeshwarv]

Dear Macropod,

It is indeed not a easy task that's why i need your help. See i am trying to do this but due to the inconsistant layouts which you have point out it becoming very time consuming process. same thing is with the contact field. I simply want to convert the row into column and am really very confused.

Please help if this could be done !!!!!!!!!!!!!

Warm Regards
Yogeshwar

[macropod]

Hi Yogeshwar,

As indicated in my previous post, your data structure is inconsistent. That would make it difficult, if not impossible, to generate a well-arranged export format.

As I also said, all this could be dealt with in code PROVIDED one can determine a clear set of rules as to how the inconsistencies can be identified and addressed. So far, you've provided nothing useful in that regard.

[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!

[macropod]

Hi parttime guy,

transferring the data to excel in a transposed format isn't difficult.

The problem is with the data. If you take a look at it you'll see its quite disorganised and the OP hasn't given any indication that clear rules exist to govern how the inconsistencies in definitions and content can be addressed. How many more inconsitencies etc might be found in a larger sample???

Consequently, There'll have to be much manual massaging of the data, either before or after they've been processed. IMHO, doing it beforehand in Word is easier.

[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!