Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Excel Formats and IF
Hi there
I'm doing a mailmerge from an excel spreadsheet. It has a variety of time values, dollar amounts, and rounding. While I have worked out how to write the code to include the formatting, how do I extend this so that blanks show as either nothing or N/A? Time fields currently have this: { MERGEFIELD Mon_Start \@HH:mm } Blank time fields are showing as "00:00" and I need them to be blank Rounded fields currently have this: { MERGEFIELD Hourly_Rate \# "0.000000" } Blank fields are showing as "0.000000" and I need them to be "N/A" |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Excel Formats and IF
{ IF { MERGEFIELD Mon_Start \@HH:mm } = "00:00" " " { MERGEFIELD Mon_Start
\@HH:mm } } { IF { MERGEFIELD Hourly_Rate \# "0.000000" } = "0.000000" "N/A" { MERGEFIELD Hourly_Rate \# "0.000000" } } or { IF { MERGEFIELD Mon_Start } = "" " " { MERGEFIELD Mon_Start \@HH:mm } } { IF { MERGEFIELD Hourly_Rate } = "" "N/A" { MERGEFIELD Hourly_Rate \# "0.000000" } } -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Sara" wrote in message ... Hi there I'm doing a mailmerge from an excel spreadsheet. It has a variety of time values, dollar amounts, and rounding. While I have worked out how to write the code to include the formatting, how do I extend this so that blanks show as either nothing or N/A? Time fields currently have this: { MERGEFIELD Mon_Start \@HH:mm } Blank time fields are showing as "00:00" and I need them to be blank Rounded fields currently have this: { MERGEFIELD Hourly_Rate \# "0.000000" } Blank fields are showing as "0.000000" and I need them to be "N/A" |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Excel Formats and IF
A simpler alternative for the hourly rate format would be
{ Mergefield Hourly_Rate \# "0.00000;;N/A" } -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Doug Robbins - Word MVP wrote: { IF { MERGEFIELD Mon_Start \@HH:mm } = "00:00" " " { MERGEFIELD Mon_Start \@HH:mm } } { IF { MERGEFIELD Hourly_Rate \# "0.000000" } = "0.000000" "N/A" { MERGEFIELD Hourly_Rate \# "0.000000" } } or { IF { MERGEFIELD Mon_Start } = "" " " { MERGEFIELD Mon_Start \@HH:mm } } { IF { MERGEFIELD Hourly_Rate } = "" "N/A" { MERGEFIELD Hourly_Rate \# "0.000000" } } "Sara" wrote in message ... Hi there I'm doing a mailmerge from an excel spreadsheet. It has a variety of time values, dollar amounts, and rounding. While I have worked out how to write the code to include the formatting, how do I extend this so that blanks show as either nothing or N/A? Time fields currently have this: { MERGEFIELD Mon_Start \@HH:mm } Blank time fields are showing as "00:00" and I need them to be blank Rounded fields currently have this: { MERGEFIELD Hourly_Rate \# "0.000000" } Blank fields are showing as "0.000000" and I need them to be "N/A" |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Excel Formats and IF
What am I doing wrong?
If I put this in: { IF { MERGEFIELD Mon_Start } = "" " " { MERGEFIELD Mon_Start \@HH:mm } } The merge result is "Mon_Start" rather than the actual value from the spreadsheet If I put this in: { IF { MERGEFIELD Mon_Start \@HH:mm } = "00:00" " " { MERGEFIELD Mon_Start \@HH:mm } } The merge result is "Error! Too many picture switches defined" "Graham Mayor" wrote: A simpler alternative for the hourly rate format would be { Mergefield Hourly_Rate \# "0.00000;;N/A" } -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Doug Robbins - Word MVP wrote: { IF { MERGEFIELD Mon_Start \@HH:mm } = "00:00" " " { MERGEFIELD Mon_Start \@HH:mm } } { IF { MERGEFIELD Hourly_Rate \# "0.000000" } = "0.000000" "N/A" { MERGEFIELD Hourly_Rate \# "0.000000" } } or { IF { MERGEFIELD Mon_Start } = "" " " { MERGEFIELD Mon_Start \@HH:mm } } { IF { MERGEFIELD Hourly_Rate } = "" "N/A" { MERGEFIELD Hourly_Rate \# "0.000000" } } "Sara" wrote in message ... Hi there I'm doing a mailmerge from an excel spreadsheet. It has a variety of time values, dollar amounts, and rounding. While I have worked out how to write the code to include the formatting, how do I extend this so that blanks show as either nothing or N/A? Time fields currently have this: { MERGEFIELD Mon_Start \@HH:mm } Blank time fields are showing as "00:00" and I need them to be blank Rounded fields currently have this: { MERGEFIELD Hourly_Rate \# "0.000000" } Blank fields are showing as "0.000000" and I need them to be "N/A" |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Excel Formats and IF
Are you using CTRL+F9 for the field boundary pairs {} ?
-- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Sara wrote: What am I doing wrong? If I put this in: { IF { MERGEFIELD Mon_Start } = "" " " { MERGEFIELD Mon_Start \@HH:mm } } The merge result is "Mon_Start" rather than the actual value from the spreadsheet If I put this in: { IF { MERGEFIELD Mon_Start \@HH:mm } = "00:00" " " { MERGEFIELD Mon_Start \@HH:mm } } The merge result is "Error! Too many picture switches defined" "Graham Mayor" wrote: A simpler alternative for the hourly rate format would be { Mergefield Hourly_Rate \# "0.00000;;N/A" } -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Doug Robbins - Word MVP wrote: { IF { MERGEFIELD Mon_Start \@HH:mm } = "00:00" " " { MERGEFIELD Mon_Start \@HH:mm } } { IF { MERGEFIELD Hourly_Rate \# "0.000000" } = "0.000000" "N/A" { MERGEFIELD Hourly_Rate \# "0.000000" } } or { IF { MERGEFIELD Mon_Start } = "" " " { MERGEFIELD Mon_Start \@HH:mm } } { IF { MERGEFIELD Hourly_Rate } = "" "N/A" { MERGEFIELD Hourly_Rate \# "0.000000" } } "Sara" wrote in message ... Hi there I'm doing a mailmerge from an excel spreadsheet. It has a variety of time values, dollar amounts, and rounding. While I have worked out how to write the code to include the formatting, how do I extend this so that blanks show as either nothing or N/A? Time fields currently have this: { MERGEFIELD Mon_Start \@HH:mm } Blank time fields are showing as "00:00" and I need them to be blank Rounded fields currently have this: { MERGEFIELD Hourly_Rate \# "0.000000" } Blank fields are showing as "0.000000" and I need them to be "N/A" |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintain excel formats in a word mailmerge | Mailmerge | |||
Excel Links in Word (Formats) | Microsoft Word Help | |||
Number formats from Excel into mail merge | Mailmerge | |||
Excel serial date formats | Mailmerge | |||
Wd '03-Date formats from Excel changed | Mailmerge |