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:


I just need to find that function.  If you know of a way to test for a number please let me know.
Who is Participating?
GrahamSkanConnect With a Mentor RetiredCommented:
The good news is that I can get the 1 (True) to display after Ctrl+A to select the whole document then F9 to update the fields. The bad news is that it displays that for both fields.

The DEFINED function is barely documented. That may be why it is little used. I seem to have just stumbled into a situation where it did what I needed at the time.

You don't have to modify the database, but when you do a mail merge, you are running a query to get the data.

Further advice will depend on your situation e.g. what sort of database is it? Do you have Access available. Do you have query and/or VBA skills?

Note that the query  language for MSSQL and Access both have an IsNumeric function,
I don't think there is a way to do this.  Maybe with VBA.

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.

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.

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

@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.
witzph1Author Commented:
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.
witzph1Author Commented:
I also just attached a screen shot of "how I would like it to merge."
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)
{=DEFINED ( {REF "abc"})  }

Open in new window

witzph1Author Commented:
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!
witzph1Author Commented:
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?
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,
witzph1Author Commented:
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.
witzph1Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.