PDA

View Full Version : Find, Copy Text from Word and paste in excel



abhijitdas
02-08-2013, 03:13 AM
Hello all,

We have over 3000 pages in a word document that are letters of this format.

Reference No:BANGA06254J1/JAN/2013 Date:31-JAN-2013

MATHEW JOHNSON
C.B.192,SECTOR-5
SALT LAKE CITY .
BANGALORE 560 064.

Dear Sir/Madam,

Payment of Pension for the month of JAN/2013.
We advise that an amount of Rs.***9365.00
......the rest of the letter follows.

What we need to get into excel is the name and address(in a single cell together as in the letter itself) in the next available row and then repeat it till the end of the document.

Thanks a lot.

Kenneth Hobs
02-08-2013, 08:41 AM
Welcome to the forum!

What you ask is not a trivial task. A pattern needs to be found I suspect. Try zipping and attaching a simple MSWord and XLSM file marked up as you would expect. Make the MSWord to contain say 3 letters where 3 records would be what you want put into Excel.

If you want to jump right in and try, see:
'FindReplace Text
' http://www.excelforum.com/excel-programming/682014-replace-word-in-ms-word-with-varable-from-ms-excel.html
' http://www.vbaexpress.com/forum/showthread.php?t=38958
' http://www.vbaexpress.com/forum/showthread.php?p=250215
' http://www.vbaexpress.com/forum/showthread.php?t=42833
' http://support.microsoft.com/kb/240157
' http://word.tips.net/T001833_Generating_a_Count_of_Word_Occurrences.html
' http://www.excelforum.com/excel-programming/794297-struggling-with-a-find-replace-macro-to-word.html

abhijitdas
02-09-2013, 01:39 AM
Thanks a lot for your reply Kenneth. I am not looking for a find and replace action. What I am looking forward to do is -

1. Find the text right after 'Date:31-Jan-2013' and before 'Dear Sir/Madam' (the name and address).
2. Copy the text.
3. Insert it in an excel sheet in the next available row.
4. Repeat the tasks 1, 2 and 3 till the end of the word document.

Hope I have been able to explain it. I am attaching a sample document with three letters for further clarity.

Thanks again. Really appreciate your help.

Aussiebear
02-09-2013, 04:19 AM
How are the letters within the word document constructed? Would it be correct to assume they are formed from a mail merge? Would it be simpler to gain access to the Client List that is merged into the series of letters?

In Post #1 you suggest that "we need to get into Excel is the name and address( in a single cell together as in the letter itself) in the next available row and then repeat it till the end of the document.

Stripping all of the Clients details out into a single cell will create a nightmare for you. May I suggest that it would be more functional to strip and store the data into columns of data with each client's details forming a row. Please indicate which way you wish to proceed with this issue.

abhijitdas
02-09-2013, 08:29 AM
You're absolutely right Aussiebear. It is formed from a mail merge. But unfortunately, the creators lost the source list and now I'm left with this only. May be I've not been able to explain properly. So I'm attaching a sample excel file (data copied manually), showing how I need the data to be copied. The corresponding sample word file has been uploaded by me in my previous post. Hope this helps.

Thanks a lot for your reply.

Cheers.