Solved

Does Word have a ISNUMBER field like Excel?

Posted on 2014-03-31
13
450 Views
Last Modified: 2014-04-03
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.
0
Comment
Question by:witzph1
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 9

Expert Comment

by:rfportilla
Comment Utility
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.
0
 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
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
0
 
LVL 9

Expert Comment

by:rfportilla
Comment Utility
@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.
0
 

Author Comment

by:witzph1
Comment Utility
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
0
 

Author Comment

by:witzph1
Comment Utility
I also just attached a screen shot of "how I would like it to merge."
How-I-would-like-it-to-merge.png
0
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
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

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:witzph1
Comment Utility
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!
0
 

Author Comment

by:witzph1
Comment Utility
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
0
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
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,
0
 

Author Comment

by:witzph1
Comment Utility
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
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
Comment Utility
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,
0
 

Author Comment

by:witzph1
Comment Utility
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.
0
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
Thanks and good luck
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Do you ever need to create a 20 page Word document for some testing purpose? Are you tired of copying & pasting old boring "lorem ipsum" text over and over again, increasing font size and line space in order to make the document 20+ pages long? Look…
If you work with Word a lot, you probably use styles. If you use styles a lot, you've probably balled your fist more often than not when working with the ribbon. In Word 2007/2010, one of the things that I find missing when using styles is a quic…
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now