exporting word merge document back to excel

[Mike]

Here is my problem. I created an excel document of your basic name address c/s/z to merge into word for labels. that works great. the person that is using this file updated the word document and not the source excel worksheet. is there an easy way to export the label word document back into an excel file that will be in the correct format of name, address, city, state, zip as seperate fields? any help would be appreciated
thanks
Mike

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?TWlrZQ==?=,

I created an excel document of your basic name address c/s/z to merge into

word for labels. that works great. the person that is using this file
updated the word document and not the source excel worksheet. is there an
easy way to export the label word document back into an excel file that will
be in the correct format of name, address, city, state, zip as seperate
fields?Easy? That depends on your definition

I'd say, start with Graham Mayor's site, where he discusses turning a label
document into a merge data source. If that helps you get things back into a
Word table with the correct structure, then copy/paste would be the easiest
way to get it back to Excel...

www.gmayor.com

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[Mike]

Thanks for the link. it was looking good until i came to the end where it says to convert the text back to a table. When i go to convert, both options are greyed out and not available. am i missing something

thanks again for the info. gmayor.com looks very helpful
Mike

 

[Mike]

well, i was able to get the text to convert to a table but it is not seperating out the city, state and zip into seperate fields. i knew this was not going to be easy. i guess i will have them go through and edit the excel worksheet to make the updates that were made in the word merge document.

thanks again for your help.
Mike

 

[Peter Jamieson]

I think if your city, state zip layout is the standard US one you may be
able to use the following sequence of find/replaces in conjunction with
Graham's article (you need to do the following before you convert to the
table) to get a bit closer - you may need to modify the sequence to suit
your data, which I appreciate isn't easy but it's probably worth a try.

First, you need to uncheck
Tools|AutoCorrect|"AutoFormat as you Type"|"Replace as you type"|"Straight
quotes with smart quotes"
and
Tools|AutoCorrect|AutoFormat|"Replace as you type"|"Straight quotes with
smart quotes"

Then, you need to make sure all the lines end in the same character (I use a
paragraph mark here). If they end in line feeds, do a replace of ^l by ^p.

In the Find/Replace dialog box, check the Wildcard option then do the
following finds/replaces, where the first line of each pair goes inthe Find
What and the second goes in the Replace With box.

^13([!,^13]@),([!0-9]@)([0-9]@)^13^13
^p\1^p\2^p\3^p¬^p

@^13
^p

,^13
^p

^13 @
^p

^13
",^p"

,^13"¬",
nothing

,^13
,


--
Peter Jamieson - Word MVP
Word MVP web site http://www.mvps.org/word

well, i was able to get the text to convert to a table but it is not

seperating out the city, state and zip into seperate fields. i knew this
was not going to be easy. i guess i will have them go through and edit the
excel worksheet to make the updates that were made in the word merge
document.

 

[Graham Mayor]

If Peter's suggestion doesn't work and you wish to post a page of your
labels to me, I will see if I can spot the problem.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>>< ><<>
Graham Mayor - Word MVP
E-mail (e-mail address removed)
Web site www.gmayor.com
Word MVP web site www.mvps.org/word
<>>< ><<> ><<> <>>< ><<> <>>< <>>< ><<>

I think if your city, state zip layout is the standard US one you may
be able to use the following sequence of find/replaces in
conjunction with Graham's article (you need to do the following
before you convert to the table) to get a bit closer - you may need
to modify the sequence to suit your data, which I appreciate isn't
easy but it's probably worth a try.

First, you need to uncheck
Tools|AutoCorrect|"AutoFormat as you Type"|"Replace as you
type"|"Straight quotes with smart quotes"
and
Tools|AutoCorrect|AutoFormat|"Replace as you type"|"Straight quotes
with smart quotes"

Then, you need to make sure all the lines end in the same character
(I use a paragraph mark here). If they end in line feeds, do a
replace of ^l by ^p.

In the Find/Replace dialog box, check the Wildcard option then do the
following finds/replaces, where the first line of each pair goes
inthe Find What and the second goes in the Replace With box.

^13([!,^13]@),([!0-9]@)([0-9]@)^13^13
^p\1^p\2^p\3^p¬^p

@^13
^p

,^13
^p

^13 @
^p

^13
",^p"

,^13"¬",
nothing

,^13
,

well, i was able to get the text to convert to a table but it is not

seperating out the city, state and zip into seperate fields. i knew
this was not going to be easy. i guess i will have them go through
and edit the excel worksheet to make the updates that were made in
the word merge document.

thanks again for your help.
Mike

 

[Mike]

Thank you. I have to go into a meeting but i will try it when i get back.
thanks again for the suggestions.
Mike

 

[Mike]

So close here. I completed the first set up instructions. When i try any of the find/replace items in the previous post, it says 0 found. Here is a sample of what the file looks like at this point

Vox Axxxxtt xxxxxxxx Kixxxlle, MO 6350
Dxxxis E Dexxxd Acxxn 907 W Mxxxde Dr Fulton, MO 6525
Jexxe W Adxxr 433 Wxxst xxle xve Ursa, IL 6237
Axxxxxn xF Axxs 2267 Hxxy 1xxxx Philadelphia, MO 6346
xxde E xxxms 33085 N Ix xxxx Rd Stover, MO 6507
Jxxxl A Axs 26655 Sxe Hxxxxxy OO Bevier, MO 6353
xxx E xxxxxon 1407 Vxxxy Hannibal, MO 6340
Lorxxxxxnxxe xxxlt 3270 Sxxxte Rxe Vxx Glasgow, MO 6525

sorry i had to change the names & addresses but i hope this helps

 

[Mike]

I think i got it figured out. i just did a simple replacement of , with ^t to seperate the city & State and then I did a similar thing for the zip code. luckily, this document has all zip codes that start with 6 with a few exceptions

thanks for the help & direction
Mik