Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merge field+if statement+phone numbers
I have a form file in Word 2000 that merges with an Excel Database. One of
my fields is a telephone number. I want to do two things with that telephone number. One format as (000) 000-0000 and if that Excel telephone field is blank when the merge takes place, I do not want the telephone number to print at all. I want the field to appear blank on the final merged document. However, if there is a telephone number in that field then I want the telephone number to print in the format shown above. I have tried several versions of an if statement and cannot seem to obtain the correct statement. Here is what I have tried: {IF{MERGEFIELD EmployerPhone}"""{MERGEFIELD EmployerPhone}"} Also tried: {IF{MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000"}"""{MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000"}"} and also: {MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000"}{IF{MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000"}"""{MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000"}"} What am I doing wrong. I have tried merging with files that contain telephone numbers and are blank. My results are not consistent. I can get it to print in the format I want depending on the above statement variation, but not consistently blank when the field is blank and not consistently print the phone number when the filed is not blank. Help!!! AJ -- John R. |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merge field+if statement+phone numbers
If you insert
{ MERGEFIELD EmployerPhone } what results do you see? Where you have blank in Excel, do you get blank in Word? Or do you sometimes get 0 or even 0.0 ? Where you have a phone number such as 5551234567 in Excel, what do you see in Word? 5551234567 ? (555)-123-4567 ? or perhaps even 5551234567.0 ? If you only ever get blank or 5551234567, then { MERGEFIELD EmployerPhone \#"(000)'-'000'-'0000" } should be enough. If you get 0 where you have blank in Excel, you can try { IF { MERGEFIELD EmployerPhone } 0 "{ MERGEFIELD EmployerPhone \#"(000)'-'000'-'0000" }" "" } (You don't have to split it across rows as I have done). Peter Jamieson "John" wrote in message news I have a form file in Word 2000 that merges with an Excel Database. One of my fields is a telephone number. I want to do two things with that telephone number. One format as (000) 000-0000 and if that Excel telephone field is blank when the merge takes place, I do not want the telephone number to print at all. I want the field to appear blank on the final merged document. However, if there is a telephone number in that field then I want the telephone number to print in the format shown above. I have tried several versions of an if statement and cannot seem to obtain the correct statement. Here is what I have tried: {IF{MERGEFIELD EmployerPhone}"""{MERGEFIELD EmployerPhone}"} Also tried: {IF{MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000"}"""{MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000"}"} and also: {MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000"}{IF{MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000"}"""{MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000"}"} What am I doing wrong. I have tried merging with files that contain telephone numbers and are blank. My results are not consistent. I can get it to print in the format I want depending on the above statement variation, but not consistently blank when the field is blank and not consistently the phone number when the filed is not blank. Help!!! AJ -- John R. |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merge field+if statement+phone numbers
Thanks for your help.
In reply to your questions: Excel field is 5550002417 format See below also -- John R. "Peter Jamieson" wrote: If you insert { MERGEFIELD EmployerPhone } what results do you see? number unformated just as in Excel - 5550002417 Where you have blank in Excel, do you get blank in Word? Or do you sometimes get 0 or even 0.0 ? Actually I get a strange number 000-000-5245 Where you have a phone number such as 5551234567 in Excel, what do you see in Word? 5551234567 ? Yes (555)-123-4567 ? Only if I code with the merge code and then the switch to put the () and dashes. or perhaps even 5551234567.0 ? NO. If you only ever get blank or 5551234567, then { MERGEFIELD EmployerPhone \#"(000)'-'000'-'0000" } should be enough. If you get 0 where you have blank in Excel, you can try { IF { MERGEFIELD EmployerPhone } 0 "{ MERGEFIELD EmployerPhone \#"(000)'-'000'-'0000" }" "" } I can easily get the number to format with the correct () and dashes using just the switch to do so and the MERGEFIELD Employer Phone. However, trying to add in a statement that indicates that if the Excel field is blank then print nothing in the merge final document and if the Excel field has a number 5552140000 then print that number but formated as (555)-214-0000. Thank you, AJ (You don't have to split it across rows as I have done). Peter Jamieson "John" wrote in message news I have a form file in Word 2000 that merges with an Excel Database. One of my fields is a telephone number. I want to do two things with that telephone number. One format as (000) 000-0000 and if that Excel telephone field is blank when the merge takes place, I do not want the telephone number to print at all. I want the field to appear blank on the final merged document. However, if there is a telephone number in that field then I want the telephone number to print in the format shown above. I have tried several versions of an if statement and cannot seem to obtain the correct statement. Here is what I have tried: {IF{MERGEFIELD EmployerPhone}"""{MERGEFIELD EmployerPhone}"} Also tried: {IF{MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000"}"""{MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000"}"} and also: {MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000"}{IF{MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000"}"""{MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000"}"} What am I doing wrong. I have tried merging with files that contain telephone numbers and are blank. My results are not consistent. I can get it to print in the format I want depending on the above statement variation, but not consistently blank when the field is blank and not consistently the phone number when the filed is not blank. Help!!! AJ -- John R. |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merge field+if statement+phone numbers
If you're getting
000-000-5245 when you should be getting blank then nothing you do with formatting switches in Word is likely to work. If you're connecting with DDE (the default) you may be seeing the type of problem described in the following article: http://support.microsoft.com/kb/204542/en-us I can't check with Word/Excel 2000 right now, but if possible I would probably go into each Excel cell in your EmployerPhone column that should be blank and a. use Edit|Clear|All to ensure it's blank b. consider changing the cell format (Format|Cells|Number) to General or Text. I'm not sure that will make any difference but you could try it with a couple of the offending cells and see what happens. If you just have data and no cell formulas etc., you could also consider recreating your sheet by exporting as .csv and re-importing. Peter Jamieson "John" wrote in message ... Thanks for your help. In reply to your questions: Excel field is 5550002417 format See below also -- John R. "Peter Jamieson" wrote: If you insert { MERGEFIELD EmployerPhone } what results do you see? number unformated just as in Excel - 5550002417 Where you have blank in Excel, do you get blank in Word? Or do you sometimes get 0 or even 0.0 ? Actually I get a strange number 000-000-5245 Where you have a phone number such as 5551234567 in Excel, what do you see in Word? 5551234567 ? Yes (555)-123-4567 ? Only if I code with the merge code and then the switch to put the () and dashes. or perhaps even 5551234567.0 ? NO. If you only ever get blank or 5551234567, then { MERGEFIELD EmployerPhone \#"(000)'-'000'-'0000" } should be enough. If you get 0 where you have blank in Excel, you can try { IF { MERGEFIELD EmployerPhone } 0 "{ MERGEFIELD EmployerPhone \#"(000)'-'000'-'0000" }" "" } I can easily get the number to format with the correct () and dashes using just the switch to do so and the MERGEFIELD Employer Phone. However, trying to add in a statement that indicates that if the Excel field is blank then print nothing in the merge final document and if the Excel field has a number 5552140000 then print that number but formated as (555)-214-0000. Thank you, AJ (You don't have to split it across rows as I have done). Peter Jamieson "John" wrote in message news I have a form file in Word 2000 that merges with an Excel Database. One of my fields is a telephone number. I want to do two things with that telephone number. One format as (000) 000-0000 and if that Excel telephone field is blank when the merge takes place, I do not want the telephone number to print at all. I want the field to appear blank on the final merged document. However, if there is a telephone number in that field then I want the telephone number to in the format shown above. I have tried several versions of an if statement and cannot seem to obtain the correct statement. Here is what I have tried: {IF{MERGEFIELD EmployerPhone}"""{MERGEFIELD EmployerPhone}"} Also tried: {IF{MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000"}"""{MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000"}"} and also: {MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000"}{IF{MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000"}"""{MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000"}"} What am I doing wrong. I have tried merging with files that contain telephone numbers and are blank. My results are not consistent. I can get it to print in the format I want depending on the above statement variation, but not consistently blank when the field is blank and not consistently the phone number when the filed is not blank. Help!!! AJ -- John R. |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merge field+if statement+phone numbers
Thank you Peter. You have been a great help.
My problem was partly due to my error. I had assigned another person to update the Excel telephone number fields to be 0000000000 instead of 000-000-0000. I just happened to be using the particular field as my test field for this project that had not been correctly updated. Anyway, the statement that finally worked correctly for me is the following: {MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000}{IF{MERGEFIELD EmployerPhone}""""}. Sorry to have caused you so much work. I am just thankful that it finally works. Take care. AJ -- John R. "Peter Jamieson" wrote: If you're getting 000-000-5245 when you should be getting blank then nothing you do with formatting switches in Word is likely to work. If you're connecting with DDE (the default) you may be seeing the type of problem described in the following article: http://support.microsoft.com/kb/204542/en-us I can't check with Word/Excel 2000 right now, but if possible I would probably go into each Excel cell in your EmployerPhone column that should be blank and a. use Edit|Clear|All to ensure it's blank b. consider changing the cell format (Format|Cells|Number) to General or Text. I'm not sure that will make any difference but you could try it with a couple of the offending cells and see what happens. If you just have data and no cell formulas etc., you could also consider recreating your sheet by exporting as .csv and re-importing. Peter Jamieson "John" wrote in message ... Thanks for your help. In reply to your questions: Excel field is 5550002417 format See below also -- John R. "Peter Jamieson" wrote: If you insert { MERGEFIELD EmployerPhone } what results do you see? number unformated just as in Excel - 5550002417 Where you have blank in Excel, do you get blank in Word? Or do you sometimes get 0 or even 0.0 ? Actually I get a strange number 000-000-5245 Where you have a phone number such as 5551234567 in Excel, what do you see in Word? 5551234567 ? Yes (555)-123-4567 ? Only if I code with the merge code and then the switch to put the () and dashes. or perhaps even 5551234567.0 ? NO. If you only ever get blank or 5551234567, then { MERGEFIELD EmployerPhone \#"(000)'-'000'-'0000" } should be enough. If you get 0 where you have blank in Excel, you can try { IF { MERGEFIELD EmployerPhone } 0 "{ MERGEFIELD EmployerPhone \#"(000)'-'000'-'0000" }" "" } I can easily get the number to format with the correct () and dashes using just the switch to do so and the MERGEFIELD Employer Phone. However, trying to add in a statement that indicates that if the Excel field is blank then print nothing in the merge final document and if the Excel field has a number 5552140000 then print that number but formated as (555)-214-0000. Thank you, AJ (You don't have to split it across rows as I have done). Peter Jamieson "John" wrote in message news I have a form file in Word 2000 that merges with an Excel Database. One of my fields is a telephone number. I want to do two things with that telephone number. One format as (000) 000-0000 and if that Excel telephone field is blank when the merge takes place, I do not want the telephone number to print at all. I want the field to appear blank on the final merged document. However, if there is a telephone number in that field then I want the telephone number to in the format shown above. I have tried several versions of an if statement and cannot seem to obtain the correct statement. Here is what I have tried: {IF{MERGEFIELD EmployerPhone}"""{MERGEFIELD EmployerPhone}"} Also tried: {IF{MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000"}"""{MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000"}"} and also: {MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000"}{IF{MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000"}"""{MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000"}"} What am I doing wrong. I have tried merging with files that contain telephone numbers and are blank. My results are not consistent. I can get it to print in the format I want depending on the above statement variation, but not consistently blank when the field is blank and not consistently the phone number when the filed is not blank. Help!!! AJ -- John R. |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merge field+if statement+phone numbers
Hi John,
If your Excel fields are all 5550002417 format, you don't need an IF test. You could use: {MERGEFIELD EmployerPhone \# "(000)'-'000'-'0000;;"} This will suppress the display if there is no phone number. Cheers -- macropod [MVP - Microsoft Word] ------------------------- "John" wrote in message ... Thank you Peter. You have been a great help. My problem was partly due to my error. I had assigned another person to update the Excel telephone number fields to be 0000000000 instead of 000-000-0000. I just happened to be using the particular field as my test field for this project that had not been correctly updated. Anyway, the statement that finally worked correctly for me is the following: {MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000}{IF{MERGEFIELD EmployerPhone}""""}. Sorry to have caused you so much work. I am just thankful that it finally works. Take care. AJ -- John R. "Peter Jamieson" wrote: If you're getting 000-000-5245 when you should be getting blank then nothing you do with formatting switches in Word is likely to work. If you're connecting with DDE (the default) you may be seeing the type of problem described in the following article: http://support.microsoft.com/kb/204542/en-us I can't check with Word/Excel 2000 right now, but if possible I would probably go into each Excel cell in your EmployerPhone column that should be blank and a. use Edit|Clear|All to ensure it's blank b. consider changing the cell format (Format|Cells|Number) to General or Text. I'm not sure that will make any difference but you could try it with a couple of the offending cells and see what happens. If you just have data and no cell formulas etc., you could also consider recreating your sheet by exporting as .csv and re-importing. Peter Jamieson "John" wrote in message ... Thanks for your help. In reply to your questions: Excel field is 5550002417 format See below also -- John R. "Peter Jamieson" wrote: If you insert { MERGEFIELD EmployerPhone } what results do you see? number unformated just as in Excel - 5550002417 Where you have blank in Excel, do you get blank in Word? Or do you sometimes get 0 or even 0.0 ? Actually I get a strange number 000-000-5245 Where you have a phone number such as 5551234567 in Excel, what do you see in Word? 5551234567 ? Yes (555)-123-4567 ? Only if I code with the merge code and then the switch to put the () and dashes. or perhaps even 5551234567.0 ? NO. If you only ever get blank or 5551234567, then { MERGEFIELD EmployerPhone \#"(000)'-'000'-'0000" } should be enough. If you get 0 where you have blank in Excel, you can try { IF { MERGEFIELD EmployerPhone } 0 "{ MERGEFIELD EmployerPhone \#"(000)'-'000'-'0000" }" "" } I can easily get the number to format with the correct () and dashes using just the switch to do so and the MERGEFIELD Employer Phone. However, trying to add in a statement that indicates that if the Excel field is blank then print nothing in the merge final document and if the Excel field has a number 5552140000 then print that number but formated as (555)-214-0000. Thank you, AJ (You don't have to split it across rows as I have done). Peter Jamieson "John" wrote in message news I have a form file in Word 2000 that merges with an Excel Database. One of my fields is a telephone number. I want to do two things with that telephone number. One format as (000) 000-0000 and if that Excel telephone field is blank when the merge takes place, I do not want the telephone number to print at all. I want the field to appear blank on the final merged document. However, if there is a telephone number in that field then I want the telephone number to in the format shown above. I have tried several versions of an if statement and cannot seem to obtain the correct statement. Here is what I have tried: {IF{MERGEFIELD EmployerPhone}"""{MERGEFIELD EmployerPhone}"} Also tried: {IF{MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000"}"""{MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000"}"} and also: {MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000"}{IF{MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000"}"""{MERGEFIELD EmployerPhone\#"(000)'-'000'-'0000"}"} What am I doing wrong. I have tried merging with files that contain telephone numbers and are blank. My results are not consistent. I can get it to print in the format I want depending on the above statement variation, but not consistently blank when the field is blank and not consistently the phone number when the filed is not blank. Help!!! AJ -- John R. |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I format a phone number in a mail merge field? | Mailmerge | |||
Word: IF statement to merge a field only if another Field is popul | Mailmerge | |||
how do I get phone numbers to format correctly in a mail merge? | Mailmerge | |||
Troubleshoot using field switching to format phone numbers in Word | Mailmerge | |||
Home Phone field not getting picked up in Merge | Mailmerge |