Merge Inserts Zeros

[Michael Koerner]

I am using Office 2002, when I merge using Excel as the data source. I have one
field called Age if blank in Excel, inserts a '0' when the merge is run from
Word. How do I get it to leave the field blank in Word if it is blank in Excel?

 

[BS]

Hi Michael;

Write the code below into field codes:

{ IF { MERGEFIELD NUMBER } = 0 “” { MERGEFIELD NUMBER } }



NUMBER= The code name in your file



Do not forget to use CTRL+F9 to insert brackets.

 

[Michael Koerner]

I must be missing something. Entered your code, now if there is an actual value
in that cell it does not insert it. All the records are blank.

--

Regards
Michael Koerner


Hi Michael;

Write the code below into field codes:

{ IF { MERGEFIELD NUMBER } = 0 “” { MERGEFIELD NUMBER } }



NUMBER= The code name in your file



Do not forget to use CTRL+F9 to insert brackets.

 

[Doug Robbins - Word MVP]

Hi Michael,

Use

{ IF { MERGEFIELD NUMBER } = "" "" { MERGEFIELD NUMBER } }

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Michael Koerner]

Thanks Doug, I can't get the damn thing to work. It either puts a zero "0" in
the cell when I don't add your code, or all the cells merge blank if I do add
your code. Yet, if I run the same merge using Word/Excel 2003 not a problem.
Unfortunately 2003 is not always the vehicles used for the merge.

I cannot understand if the cell is empty in Excel why Word would merge, and
insert a '0'

--

Regards
Michael Koerner


Hi Michael,

Use

{ IF { MERGEFIELD NUMBER } = "" "" { MERGEFIELD NUMBER } }

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Doug Robbins - Word MVP]

Hi Michael,

Can you be sure that the cell is empty and does not contain a space. It may
be best to do the If..Then.. Else in Excel.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Michael Koerner]

The cell is definitely empty. Data is only inserted if there is a value to put
in the cell. Also, why would it work without any intervention in 2003, and not
in 2002? I suppose I could delete all the zero's <g> after the merge.

--

Regards
Michael Koerner


Hi Michael,

Can you be sure that the cell is empty and does not contain a space. It may
be best to do the If..Then.. Else in Excel.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Cindy M -WordMVP-]

Hi Michael,

I must be missing something. Entered your code, now if there is an actual value
in that cell it does not insert it. All the records are blank.

{ IF { MERGEFIELD NUMBER } = 0 “” { MERGEFIELD NUMBER } }

Word can get really picky about numbers and such... Try it this way (pay special
attention to the "quotes"):

{ IF "{ MERGEFIELD NUMBER }" = "0" "" "{ MERGEFIELD NUMBER }" }

Another thing you could try would be to explicitly format that column as text,
using the Text to column tool in Excel's DATA menu. The other way to go would be
to use a different connection method (DDE or Word's internal spreadsheet
converter, for example). You'll find instructions for that in the Word 2002
section of my website.

I imagine the reason you see a different behavior in Office 2003 is because so
many people complained of exactly this behavior in Word 2002 And I believe it
originates in how the ODBC driver (which OLEDB uses for Excel) picks up data from
"numerical" columns.

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

 

[Michael Koerner]

Cindy;

Thanks, that did the trick. Your help is greatly appreciated.

--

Regards
Michael Koerner


Hi Michael,

I must be missing something. Entered your code, now if there is an actual value
in that cell it does not insert it. All the records are blank.

{ IF { MERGEFIELD NUMBER } = 0 "" { MERGEFIELD NUMBER } }

Word can get really picky about numbers and such... Try it this way (pay special
attention to the "quotes"):

{ IF "{ MERGEFIELD NUMBER }" = "0" "" "{ MERGEFIELD NUMBER }" }

Another thing you could try would be to explicitly format that column as text,
using the Text to column tool in Excel's DATA menu. The other way to go would be
to use a different connection method (DDE or Word's internal spreadsheet
converter, for example). You'll find instructions for that in the Word 2002
section of my website.

I imagine the reason you see a different behavior in Office 2003 is because so
many people complained of exactly this behavior in Word 2002 And I believe
it
originates in how the ODBC driver (which OLEDB uses for Excel) picks up data
from
"numerical" columns.

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

 

[Cindy M -WordMVP-]

Hi Michael,

Thanks, that did the trick.

Which suggestion ? (Just so we can keep track...)

Cindy Meister