| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Using IF function for 5 variables
I am an infant school teacher who has recently stumbled upon the uses of mail
merge. I have limited knowledge of format switches and have found advice on this forum very clear and useful. I have looked at; Mail merge - Ask / If function posted on 5/5/2006 6:55 AM PST http://www.microsoft.com/communities...e34-4b0c-8aba- 2e17252d39bc&lang=en&cr=us&pt=&catlist=&dglist=&pt list=&exp=&sloc=en-us The data I have merged results in either a four, three, two, or one digit number appearing in the word doc. I have been trying to use the IF switch to add letters before the resulting Mergefield number. For example, 1.If the number is 9 or less than 9 (ie, 9 8 7 6 5 4 3 2 1 0) then a U would appear before the Mergefield number,eg, U9 2.If the number is between 99 and 10 (including 99 and 10) then a T U would appear before the Mergefield number, eg, TU21 3.If the number is between 999 and 100 (including 999 and 100) then H T U would appear before the Mergefield number, eg, HTU354 4.If, the number is between 9999 and 1000 (including 999 and 100) the Th H T U would appear before the Mergefield number, eg, Th H T U 3437 5. Finally, the Mergefield may sometimes not result in number being displayed in the word doc, intead a symbol or a word. This symbol or word will be displayed on its own - without any Th H T U, H T U, T U or U in front of it. I have included an example (probably a very crude one of where I am up to! { If Qn_1_Red_410=U{MERGEFIELD Qn_1_Red_4} } I have been trying to use the IF function, and have managed to come up with the above code, but am stumped further. Any advice woud be warmly welcomed. Matthew |
|
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Using IF function for 5 variables
Hi Matthew,
Try: {QUOTE{SET Val {MERGEFIELD Qn_1_Red_4}}{IF{Val}= {=Val} {IF{Val} 10 "U" {IF{Val} 100 "TU" {IF{Val} 1000 "HTU" "Th HTU"}}} {Val}}} or {QUOTE{SET Val «Qn_1_Red_4»}{IF{Val}= {=Val} {IF{Val} 10 "U" {IF{Val} 100 "TU" {IF{Val} 1000 "HTU" "Th HTU"}}} {Val}}} Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message. Likwise, you can't type or copy & paste the chevrons (ie '« »') - they're part of the actual mergefields, which you can insert from the mailmerge toolbar. -- Cheers macropod [Microsoft MVP - Word] "mj_bowen" wrote in message ... I am an infant school teacher who has recently stumbled upon the uses of mail merge. I have limited knowledge of format switches and have found advice on this forum very clear and useful. I have looked at; Mail merge - Ask / If function posted on 5/5/2006 6:55 AM PST http://www.microsoft.com/communities...e34-4b0c-8aba- 2e17252d39bc&lang=en&cr=us&pt=&catlist=&dglist=&pt list=&exp=&sloc=en-us The data I have merged results in either a four, three, two, or one digit number appearing in the word doc. I have been trying to use the IF switch to add letters before the resulting Mergefield number. For example, 1.If the number is 9 or less than 9 (ie, 9 8 7 6 5 4 3 2 1 0) then a U would appear before the Mergefield number,eg, U9 2.If the number is between 99 and 10 (including 99 and 10) then a T U would appear before the Mergefield number, eg, TU21 3.If the number is between 999 and 100 (including 999 and 100) then H T U would appear before the Mergefield number, eg, HTU354 4.If, the number is between 9999 and 1000 (including 999 and 100) the Th H T U would appear before the Mergefield number, eg, Th H T U 3437 5. Finally, the Mergefield may sometimes not result in number being displayed in the word doc, intead a symbol or a word. This symbol or word will be displayed on its own - without any Th H T U, H T U, T U or U in front of it. I have included an example (probably a very crude one of where I am up to! { If Qn_1_Red_410=U{MERGEFIELD Qn_1_Red_4} } I have been trying to use the IF function, and have managed to come up with the above code, but am stumped further. Any advice woud be warmly welcomed. Matthew |
|
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Using IF function for 5 variables
Hi macropod,
Thank you for your help, I am truly grateful! I would not have known where to start! The first code works really well, however, would it be possible to have the actual mergefield number appear after the text as well, eg, if the mergefield number is 34 then TU34 would appear in the word doc. At the moment, only TU shows, without the number (the same also happens for U, HTU and TH H T U). The other parts work perfectly, ie if the "=" symbol is in the excel doc instead of a digit number then it appears in the word doc. If "eighty five" (in words) is in the excel doc then "eighty five" appears in the word doc. Brilliant! I have tried fiddlling to sort out but am out of my depth! The aim of this doc is to have a sum with T (tens) U (units) etc above the number to help remind the children of the parts of a number eg, TU TU 34 + 28 (By using mail merge, I'm saving myself valuable time on creating work sheets!) P.S. I tried the second code but it only resulted in: Qn_1_Red_4 I'm not sure if I entered the correctly. I tried looking in the mailmerge task bar for the chevrons! Yours hopefully, Matthew "macropod" wrote: Hi Matthew, Try: {QUOTE{SET Val {MERGEFIELD Qn_1_Red_4}}{IF{Val}= {=Val} {IF{Val} 10 "U" {IF{Val} 100 "TU" {IF{Val} 1000 "HTU" "Th HTU"}}} {Val}}} or {QUOTE{SET Val «Qn_1_Red_4»}{IF{Val}= {=Val} {IF{Val} 10 "U" {IF{Val} 100 "TU" {IF{Val} 1000 "HTU" "Th HTU"}}} {Val}}} Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message. Likwise, you can't type or copy & paste the chevrons (ie '« »') - they're part of the actual mergefields, which you can insert from the mailmerge toolbar. -- Cheers macropod [Microsoft MVP - Word] "mj_bowen" wrote in message ... I am an infant school teacher who has recently stumbled upon the uses of mail merge. I have limited knowledge of format switches and have found advice on this forum very clear and useful. I have looked at; Mail merge - Ask / If function posted on 5/5/2006 6:55 AM PST http://www.microsoft.com/communities...e34-4b0c-8aba- 2e17252d39bc&lang=en&cr=us&pt=&catlist=&dglist=&pt list=&exp=&sloc=en-us The data I have merged results in either a four, three, two, or one digit number appearing in the word doc. I have been trying to use the IF switch to add letters before the resulting Mergefield number. For example, 1.If the number is 9 or less than 9 (ie, 9 8 7 6 5 4 3 2 1 0) then a U would appear before the Mergefield number,eg, U9 2.If the number is between 99 and 10 (including 99 and 10) then a T U would appear before the Mergefield number, eg, TU21 3.If the number is between 999 and 100 (including 999 and 100) then H T U would appear before the Mergefield number, eg, HTU354 4.If, the number is between 9999 and 1000 (including 999 and 100) the Th H T U would appear before the Mergefield number, eg, Th H T U 3437 5. Finally, the Mergefield may sometimes not result in number being displayed in the word doc, intead a symbol or a word. This symbol or word will be displayed on its own - without any Th H T U, H T U, T U or U in front of it. I have included an example (probably a very crude one of where I am up to! { If Qn_1_Red_410=U{MERGEFIELD Qn_1_Red_4} } I have been trying to use the IF function, and have managed to come up with the above code, but am stumped further. Any advice woud be warmly welcomed. Matthew . |
|
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Using IF function for 5 variables
Hi Matthew,
You could simply insert another {Val} field inside the right-most field brace: {QUOTE{SET Val {MERGEFIELD Qn_1_Red_4}}{IF{Val}= {=Val} {IF{Val} 10 "U" {IF{Val} 100 "TU" {IF{Val} 1000 "HTU" "Th HTU"}}} {Val}}{Val}} With this you'll get '7U', '23TU', '522HTU', '1024 Th HTU', 'AA', etc. Note, that this means a data field containing 'A' generates an output of 'AA'. If that's not what you intend, try moving the last {Val} field to the right instead: {QUOTE{SET Val {MERGEFIELD Qn_1_Red_4}}{IF{Val}= {=Val} {IF{Val} 10 "U" {IF{Val} 100 "TU" {IF{Val} 1000 "HTU" "Th HTU"}}}}{Val}} With this you'll get '7U', '23TU', '522HTU', '1024 Th HTU', 'A', etc (ie no duplicated 'A'). As for the chevrons, Word creates them automatically if you insert the mergefield from the mergefield selection button on the mailmerge toolbar. -- Cheers macropod [Microsoft MVP - Word] "mj_bowen" wrote in message ... Hi macropod, Thank you for your help, I am truly grateful! I would not have known where to start! The first code works really well, however, would it be possible to have the actual mergefield number appear after the text as well, eg, if the mergefield number is 34 then TU34 would appear in the word doc. At the moment, only TU shows, without the number (the same also happens for U, HTU and TH H T U). The other parts work perfectly, ie if the "=" symbol is in the excel doc instead of a digit number then it appears in the word doc. If "eighty five" (in words) is in the excel doc then "eighty five" appears in the word doc. Brilliant! I have tried fiddlling to sort out but am out of my depth! The aim of this doc is to have a sum with T (tens) U (units) etc above the number to help remind the children of the parts of a number eg, TU TU 34 + 28 (By using mail merge, I'm saving myself valuable time on creating work sheets!) P.S. I tried the second code but it only resulted in: Qn_1_Red_4 I'm not sure if I entered the correctly. I tried looking in the mailmerge task bar for the chevrons! Yours hopefully, Matthew "macropod" wrote: Hi Matthew, Try: {QUOTE{SET Val {MERGEFIELD Qn_1_Red_4}}{IF{Val}= {=Val} {IF{Val} 10 "U" {IF{Val} 100 "TU" {IF{Val} 1000 "HTU" "Th HTU"}}} {Val}}} or {QUOTE{SET Val «Qn_1_Red_4»}{IF{Val}= {=Val} {IF{Val} 10 "U" {IF{Val} 100 "TU" {IF{Val} 1000 "HTU" "Th HTU"}}} {Val}}} Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message. Likwise, you can't type or copy & paste the chevrons (ie '« »') - they're part of the actual mergefields, which you can insert from the mailmerge toolbar. -- Cheers macropod [Microsoft MVP - Word] "mj_bowen" wrote in message ... I am an infant school teacher who has recently stumbled upon the uses of mail merge. I have limited knowledge of format switches and have found advice on this forum very clear and useful. I have looked at; Mail merge - Ask / If function posted on 5/5/2006 6:55 AM PST http://www.microsoft.com/communities...e34-4b0c-8aba- 2e17252d39bc&lang=en&cr=us&pt=&catlist=&dglist=&pt list=&exp=&sloc=en-us The data I have merged results in either a four, three, two, or one digit number appearing in the word doc. I have been trying to use the IF switch to add letters before the resulting Mergefield number. For example, 1.If the number is 9 or less than 9 (ie, 9 8 7 6 5 4 3 2 1 0) then a U would appear before the Mergefield number,eg, U9 2.If the number is between 99 and 10 (including 99 and 10) then a T U would appear before the Mergefield number, eg, TU21 3.If the number is between 999 and 100 (including 999 and 100) then H T U would appear before the Mergefield number, eg, HTU354 4.If, the number is between 9999 and 1000 (including 999 and 100) the Th H T U would appear before the Mergefield number, eg, Th H T U 3437 5. Finally, the Mergefield may sometimes not result in number being displayed in the word doc, intead a symbol or a word. This symbol or word will be displayed on its own - without any Th H T U, H T U, T U or U in front of it. I have included an example (probably a very crude one of where I am up to! { If Qn_1_Red_410=U{MERGEFIELD Qn_1_Red_4} } I have been trying to use the IF function, and have managed to come up with the above code, but am stumped further. Any advice woud be warmly welcomed. Matthew . |
|
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Using IF function for 5 variables
Hi Macropod!
Thank you so much for yor quick reply! It works, fantastic! You have saved me so much time! If you ever find yourself in Cornwall, pop in for a cuppa! Best wishes, Matthew "macropod" wrote: Hi Matthew, You could simply insert another {Val} field inside the right-most field brace: {QUOTE{SET Val {MERGEFIELD Qn_1_Red_4}}{IF{Val}= {=Val} {IF{Val} 10 "U" {IF{Val} 100 "TU" {IF{Val} 1000 "HTU" "Th HTU"}}} {Val}}{Val}} With this you'll get '7U', '23TU', '522HTU', '1024 Th HTU', 'AA', etc. Note, that this means a data field containing 'A' generates an output of 'AA'. If that's not what you intend, try moving the last {Val} field to the right instead: {QUOTE{SET Val {MERGEFIELD Qn_1_Red_4}}{IF{Val}= {=Val} {IF{Val} 10 "U" {IF{Val} 100 "TU" {IF{Val} 1000 "HTU" "Th HTU"}}}}{Val}} With this you'll get '7U', '23TU', '522HTU', '1024 Th HTU', 'A', etc (ie no duplicated 'A'). As for the chevrons, Word creates them automatically if you insert the mergefield from the mergefield selection button on the mailmerge toolbar. -- Cheers macropod [Microsoft MVP - Word] "mj_bowen" wrote in message ... Hi macropod, Thank you for your help, I am truly grateful! I would not have known where to start! The first code works really well, however, would it be possible to have the actual mergefield number appear after the text as well, eg, if the mergefield number is 34 then TU34 would appear in the word doc. At the moment, only TU shows, without the number (the same also happens for U, HTU and TH H T U). The other parts work perfectly, ie if the "=" symbol is in the excel doc instead of a digit number then it appears in the word doc. If "eighty five" (in words) is in the excel doc then "eighty five" appears in the word doc. Brilliant! I have tried fiddlling to sort out but am out of my depth! The aim of this doc is to have a sum with T (tens) U (units) etc above the number to help remind the children of the parts of a number eg, TU TU 34 + 28 (By using mail merge, I'm saving myself valuable time on creating work sheets!) P.S. I tried the second code but it only resulted in: Qn_1_Red_4 I'm not sure if I entered the correctly. I tried looking in the mailmerge task bar for the chevrons! Yours hopefully, Matthew "macropod" wrote: Hi Matthew, Try: {QUOTE{SET Val {MERGEFIELD Qn_1_Red_4}}{IF{Val}= {=Val} {IF{Val} 10 "U" {IF{Val} 100 "TU" {IF{Val} 1000 "HTU" "Th HTU"}}} {Val}}} or {QUOTE{SET Val «Qn_1_Red_4»}{IF{Val}= {=Val} {IF{Val} 10 "U" {IF{Val} 100 "TU" {IF{Val} 1000 "HTU" "Th HTU"}}} {Val}}} Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message. Likwise, you can't type or copy & paste the chevrons (ie '« »') - they're part of the actual mergefields, which you can insert from the mailmerge toolbar. -- Cheers macropod [Microsoft MVP - Word] "mj_bowen" wrote in message ... I am an infant school teacher who has recently stumbled upon the uses of mail merge. I have limited knowledge of format switches and have found advice on this forum very clear and useful. I have looked at; Mail merge - Ask / If function posted on 5/5/2006 6:55 AM PST http://www.microsoft.com/communities...e34-4b0c-8aba- 2e17252d39bc&lang=en&cr=us&pt=&catlist=&dglist=&pt list=&exp=&sloc=en-us The data I have merged results in either a four, three, two, or one digit number appearing in the word doc. I have been trying to use the IF switch to add letters before the resulting Mergefield number. For example, 1.If the number is 9 or less than 9 (ie, 9 8 7 6 5 4 3 2 1 0) then a U would appear before the Mergefield number,eg, U9 2.If the number is between 99 and 10 (including 99 and 10) then a T U would appear before the Mergefield number, eg, TU21 3.If the number is between 999 and 100 (including 999 and 100) then H T U would appear before the Mergefield number, eg, HTU354 4.If, the number is between 9999 and 1000 (including 999 and 100) the Th H T U would appear before the Mergefield number, eg, Th H T U 3437 5. Finally, the Mergefield may sometimes not result in number being displayed in the word doc, intead a symbol or a word. This symbol or word will be displayed on its own - without any Th H T U, H T U, T U or U in front of it. I have included an example (probably a very crude one of where I am up to! { If Qn_1_Red_410=U{MERGEFIELD Qn_1_Red_4} } I have been trying to use the IF function, and have managed to come up with the above code, but am stumped further. Any advice woud be warmly welcomed. Matthew . . |
| Reply |
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| using bookmarks as variables - help! | Mailmerge | |||
| variables in WORD | New Users | |||
| Using variables in a document | Page Layout | |||
| Variables question | New Users | |||
| Variables? | Mailmerge | |||
![mj_bowen[_2_] is offline](images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
