Break Field apart to format

[Sandra]

I am using Word 2002.
My excel data has a quater section field (ex. "SESWSE")
I was wondering is there a way to merge that field to
appear in Word as "SE¼SW¼SE¼"

Thanks for your help on this

Sandra

 

[Charles Kenyon]

Only with a _very_ complex nested IF field. You would have to test for every
possibility. Otherwise, any chance of breaking it up in Excel to be three
cells/fields?
--

Charles Kenyon

See the MVP FAQ: <URL: http://www.mvps.org/word/> which is awesome!
--------- --------- --------- --------- --------- ---------
This message is posted to a newsgroup. Please post replies
and questions to the newsgroup so that others can learn
from my ignorance and your wisdom.

I am using Word 2002.
My excel data has a quater section field (ex. "SESWSE")
I was wondering is there a way to merge that field to
appear in Word as "SE¼SW¼SE¼"

Thanks for your help on this

Sandra

 

[Guest]

I can't edit the Excel table. I am linking to someones
data in my office. He needs the field to stay in one.
In the past I have copied his Excel worksheet and then
edited it into three. The problem that causes is this is
an ongoing project. I need to be able to merge quickly
and many different times. When I create a copy I lose
that "current" link to his data.

Sandra

 

[Charles Kenyon]

Another alternative, for post-merge documents, would be a macro that (1)
converts that field into text and (2) runs multiple replaces for the
different variations.

Look out for 1/2 and 1/3 problems, though. At least around here, those are
used in legal descriptions as well. d.e. N-1/2 of SE-1/4. How does he handle
those in the Excel field?

He should be able to add, outside of the printing area, three fields that
parse the data. Not sure how to go about doing this, it is easier (for me)
to input them as three cells and have a combination cell for print/display.

How many of these do you do? In a year? Fixing this is going to be a big
time cost for you; is it worth it compared to manually changing them?
--

Charles Kenyon

See the MVP FAQ: <URL: http://www.mvps.org/word/> which is awesome!
--------- --------- --------- --------- --------- ---------
This message is posted to a newsgroup. Please post replies
and questions to the newsgroup so that others can learn
from my ignorance and your wisdom.

I can't edit the Excel table. I am linking to someones
data in my office. He needs the field to stay in one.
In the past I have copied his Excel worksheet and then
edited it into three. The problem that causes is this is
an ongoing project. I need to be able to merge quickly
and many different times. When I create a copy I lose
that "current" link to his data.

Sandra

 

[Peter Jamieson]

There are a few general ways you can approach this:
a. change the source data
b. use a large number of IF fields (I'm not familiar with this type of
reference but assuming it always has a group of three quarters, each of
which can be NW/NE/SW/SE, then I think you would need 64 IF fields. Or use
nested IF fields (you will only increase the complexity)
b. use a method of accessing the source data that lets you specify an SQL
query, then use a string function in the query to return the string you
want. This is not trivial if you have never done it before, but if you can
work your way through MS Query it should be doable,
c. Use a DATABASE field and some SQL code to split the field up - you will
find examples posted by me in this group
d. use a "lookup" of some kind in your Word mail merge main document. E.g.
create a new Word document, then have one result text for each of the
possible 64 values, e.g. NW¼NW¼NW¼. Then bookmark each value with the
reference, so e.g. SE¼SW¼SE¼ is bookmarked using the name SESWSE. Suppose
this file is c:\myq\quarters.doc. Then use the following nested field in
your document

{ INCLUDETEXT "c:\\myq\\quartersdoc" { MERGEFIELD myquarter } }

where myquarter is the name of the Excel column containing the reference
e. Or use some VBA to generate 64 document variables or custom document
properties named NWNWNW, SESWSE etc., with the appropriate result values,
and use { DOCVARIABLE "{ MERGEFIELD myquarter }" } or { DOCPROPERTY "{
MERGEFIELD myquarter }" } to insert the correct result. You should only need
to run the VBA once when you create a new merge appllication.
f. or use Word 2002 VBA mailmerge events to inspect the value of the field
for each record and stuff the result you want into the mail merge main
document as each record in the data source is processed.



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

I am using Word 2002.
My excel data has a quater section field (ex. "SESWSE")
I was wondering is there a way to merge that field to
appear in Word as "SE¼SW¼SE¼"

Thanks for your help on this

Sandra

 

[Sandra]

I see know that it isn't worth trying to automate this process. We will just need to coordinate to make sure I have current data and then I will break the field into three. I appreciate you help on this matter

----- Charles Kenyon wrote: ----

Another alternative, for post-merge documents, would be a macro that (1
converts that field into text and (2) runs multiple replaces for th
different variations

Look out for 1/2 and 1/3 problems, though. At least around here, those ar
used in legal descriptions as well. d.e. N-1/2 of SE-1/4. How does he handl
those in the Excel field

He should be able to add, outside of the printing area, three fields tha
parse the data. Not sure how to go about doing this, it is easier (for me
to input them as three cells and have a combination cell for print/display

How many of these do you do? In a year? Fixing this is going to be a bi
time cost for you; is it worth it compared to manually changing them
--

Charles Kenyo

See the MVP FAQ: <URL: http://www.mvps.org/word/> which is awesome
--------- --------- --------- --------- --------- --------
This message is posted to a newsgroup. Please post replie
and questions to the newsgroup so that others can lear
from my ignorance and your wisdom

I can't edit the Excel table. I am linking to someone
data in my office. He needs the field to stay in one
In the past I have copied his Excel worksheet and the
edited it into three. The problem that causes is this i
an ongoing project. I need to be able to merge quickl
and many different times. When I create a copy I los
that "current" link to his data

Sandr