[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Does Word have a ISNUMBER field like Excel?

Posted on 2014-03-31
13
Medium Priority
?
479 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 9

Expert Comment

by:rfportilla
ID: 39966811
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 53

Expert Comment

by:Rgonzo1971
ID: 39966831
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
ID: 39966844
@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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:witzph1
ID: 39966878
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
ID: 39966895
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
ID: 39967852
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
 

Author Comment

by:witzph1
ID: 39969183
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
ID: 39973497
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
ID: 39973793
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
ID: 39975091
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 2000 total points
ID: 39975760
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
ID: 39976185
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
ID: 39976201
Thanks and good luck
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
This video walks the viewer through the process of creating a watermark for their document, customizing it, and saving it for viewing/printing needs.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

649 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