witzph1
asked on
Does Word have a ISNUMBER field like Excel?
I am creating a mail merge template that merges data from our company's management system. There is a field in the db titled HAZEEXPOS that could be blank, or it could contain a number, or it could contain a word. If it contains a number, that number represents a dollar amount, but the field itself does not contain a dollar symbol.
I would like to create an IF statement for my merge template that ideally would
1. leave the field blank if the field is blank
2. format the field with dollar signs if the field contains a number.
3. pull in text with no formatting if the field contains a word.
I have been able to address #1 and #2 above with this statement:
{IF "HAZEEXPOS" <> "" "{=HAZEEXPOS \# $#,###}"}"}
But I have not been able to address #3. I need to test to see if the field contains a number. I am hoping Word has a function similar to Excel's ISNUMBER, but I can't find one. Without that test, if the field contains a word I get an "Unexpected end of formula" error message when it merges.
If there was such a function, an IF statement might look something like:
{IF "HAZEEXPOS" <> "" "{ IF {= ISNUMBER (HAZEEXPOS)}=1 "{=HAZEEXPOS \# $#,###}" "HAZEEXPOS"}"}
I just need to find that function. If you know of a way to test for a number please let me know.
I would like to create an IF statement for my merge template that ideally would
1. leave the field blank if the field is blank
2. format the field with dollar signs if the field contains a number.
3. pull in text with no formatting if the field contains a word.
I have been able to address #1 and #2 above with this statement:
{IF "HAZEEXPOS" <> "" "{=HAZEEXPOS \# $#,###}"}"}
But I have not been able to address #3. I need to test to see if the field contains a number. I am hoping Word has a function similar to Excel's ISNUMBER, but I can't find one. Without that test, if the field contains a word I get an "Unexpected end of formula" error message when it merges.
If there was such a function, an IF statement might look something like:
{IF "HAZEEXPOS" <> "" "{ IF {= ISNUMBER (HAZEEXPOS)}=1 "{=HAZEEXPOS \# $#,###}" "HAZEEXPOS"}"}
I just need to find that function. If you know of a way to test for a number please let me know.
Hi
Normally you do not need it because the fomat recognize if there is a number and in that case format the number and let the text as it is.
Regards
Normally you do not need it because the fomat recognize if there is a number and in that case format the number and let the text as it is.
Regards
@Rgonzo1971, the auto detection will not allow for 2 different formats as the OP is asking, will it? It sounds like you are saying that if the source is a number, it will use the given number format and if it is not a number it will just dump the text? I don't think it works this way. Please confirm. I'm just curious. Thanks.
ASKER
I've attached a screen shot of an actual merge document. This might help show more clearly what I am trying to accomplish.
rfportilla, I understand your sentiment about controlling input, but in this case, it is a large CMS database system that I do not control. Plus, it really is valid that the field would sometimes contain a dollar amount and sometimes a word.
rgonzo1971, the database will not allow a person to enter dollar signs into the field. It allows only letters and numbers. So yes, if showing dollar signs wasn't a big deal I could skip the formatting altogether. But they are dollar amounts, and so I would like to show them as dollar amounts in the merged document.
merge-sample.png
rfportilla, I understand your sentiment about controlling input, but in this case, it is a large CMS database system that I do not control. Plus, it really is valid that the field would sometimes contain a dollar amount and sometimes a word.
rgonzo1971, the database will not allow a person to enter dollar signs into the field. It allows only letters and numbers. So yes, if showing dollar signs wasn't a big deal I could skip the formatting altogether. But they are dollar amounts, and so I would like to show them as dollar amounts in the merged document.
merge-sample.png
ASKER
I also just attached a screen shot of "how I would like it to merge."
How-I-would-like-it-to-merge.png
How-I-would-like-it-to-merge.png
The general advice is that it is easier to arrange your datasource so that these decisions don't have to be taken within the MailMerge operation.
However, if that is difficult in itself, it might help to know that I have had some success in testing for numeric with the DEFINED function in the FORMULA field. For instance this field returns 1(true) if the bookmarked location "abc" is numeric. Otherwise it returns 0 (false)
However, if that is difficult in itself, it might help to know that I have had some success in testing for numeric with the DEFINED function in the FORMULA field. For instance this field returns 1(true) if the bookmarked location "abc" is numeric. Otherwise it returns 0 (false)
{=DEFINED ( {REF "abc"}) }ASKER
GrahamSkan: interesting concept. Another friend had suggested he thought you could test for ASCII characters using something like {IF "FIELD" > "a" "it's text" "it's not text"}. I tried it and it evaluated "it's text" whether there was a number or text.
I'll give your idea a try and let you know. Thank you!
I'll give your idea a try and let you know. Thank you!
ASKER
GrahamSkan: I tried your idea in a test. First I created a bookmark to a title within my document called "Subject To Audit". (See attached screenshot "Bookmark.jpg.")
I tested the bookmark using the shortcut key CTRL+G and it correctly jumped to the bookmark. I then created just a {REF SubjectToAudit} field to make sure it pulled in the title. It did.
So then I created my full field as shown in the attached screenshot "Field-coding.jpg". When I merged the document it gave me a syntax error. I tried it surrounding the "SubjectToAudit" bookmark name with quotes and without quotes. Both times it gave my the syntax error.
Does it look like I have the field created correctly?
Bookmark.jpg
Field-coding.jpg
I tested the bookmark using the shortcut key CTRL+G and it correctly jumped to the bookmark. I then created just a {REF SubjectToAudit} field to make sure it pulled in the title. It did.
So then I created my full field as shown in the attached screenshot "Field-coding.jpg". When I merged the document it gave me a syntax error. I tried it surrounding the "SubjectToAudit" bookmark name with quotes and without quotes. Both times it gave my the syntax error.
Does it look like I have the field created correctly?
Bookmark.jpg
Field-coding.jpg
It is usually better to post the document. However, it does look OK.
I have done a bit of experimentation and found that if the bookmark contains a valid number, then the field displays "1". If it starts with a letter or any character that cannot start a number, then it displays "0".
However, if it is empty, or starts as a number, but it cannot be resolved (e.g. "1A"), then it reports a syntax error, along with the text.
In the light of this, I think you would do better by modifying the incoming data. If you can't modify it in the company database, you may be able to write a query to deliver it in a more suitable form. If you can't do that, you might want to use an intermediate datasource, such as a separate Access database or an Excel spreadsheet. There would then be a chance of using VBA if SQL isn't up to the job with your data.
Hopefully an extra single character field whose value depends on whether the data is numeric could be created. This can be tested and the extra formatting applied if it indicates that the portmanteau field is indeed numeric,
I have done a bit of experimentation and found that if the bookmark contains a valid number, then the field displays "1". If it starts with a letter or any character that cannot start a number, then it displays "0".
However, if it is empty, or starts as a number, but it cannot be resolved (e.g. "1A"), then it reports a syntax error, along with the text.
In the light of this, I think you would do better by modifying the incoming data. If you can't modify it in the company database, you may be able to write a query to deliver it in a more suitable form. If you can't do that, you might want to use an intermediate datasource, such as a separate Access database or an Excel spreadsheet. There would then be a chance of using VBA if SQL isn't up to the job with your data.
Hopefully an extra single character field whose value depends on whether the data is numeric could be created. This can be tested and the extra formatting applied if it indicates that the portmanteau field is indeed numeric,
ASKER
I've attached a test document containing two marks, one pointing to text and one pointing to a number. I then created two DEFINED fields which refer to the two different bookmarks.
When I toggle field codes off, I see the "result" of the REF fields, but the DEFINED fields show nothing at all. Do I not have them correctly done?
Since we use a 3rd party system, I can't impose any requirements on that field. If I can't get the DEFINED field to work, I may have to use your other idea of using a helper file.
Defined-field-experiment.docx
When I toggle field codes off, I see the "result" of the REF fields, but the DEFINED fields show nothing at all. Do I not have them correctly done?
Since we use a 3rd party system, I can't impose any requirements on that field. If I can't get the DEFINED field to work, I may have to use your other idea of using a helper file.
Defined-field-experiment.docx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Okay, thanks so much for your help. I think I'm finding that using Word fields, it is not possible to accomplish what I want. Creating a query might be the route to go. That is outside the scope of this question, and better posted separately under its own topic, if I go that route. Thanks so much for all your help.
Thanks and good luck
However, my usual approach to this kind of issue is preformatting. If you have control over the source data, you can make sure it is properly formatted before pulling it into word.