Multiplied number(text) not returning the correct amount

[Rick J.]

I'm working on trying to multiply a dollar amount times two. Sounds simple. The problem is the file I receive daily is a tab delimited text file where all fields have been formatted as text. My rule "2xAmt" works on amounts under $1,000.00, but I believe because of the comma any amount equal to or over $1,000.00 does not return the proper amount. I'm using the "2xAmt "rule in the "FinishingForward" rule where I'm bringing in data through javascript so the text flows properly.

There is no way to get the file formatted different than it is now. I'm stuck with what I recieve for a data file to work with.

Is there any way I can make this happen? Im attaching the files for review. Any help would be appreciated.

Thanks in advance,
Rick J.

[Dan Korn]

Yes, it's always better to deal with raw, unformatted data, such as pure numbers, rather than pre-formatted data, such as with dollar signs and comma separators. But I understand that you just have what you have.

So you're stripping off the dollar sign to get a number, but not the commas that divide thousands (and millions). There are lots of ways to "clean" or "unformat" such strings in JavaScript, which can be found via a search like this, but I think this is the simplest:

var ttnum = StringToNumber(Field("amnt").replace(/[^\d\.]/g, ''));
return FormatNumber("$#,###.00", ttnum * 2);

[Rick J.]

Thank you Dan for the quick reply. I knew there had to be a way to get rid of the commas and decimal point, but had no idea how to go about that. Is there anyway you could just explain what (/[^\d\.]/g, '') that all means. I know it's to strip all those out, but I'm very curious as to what the symbols mean. I don't see anything that tells it to remove the commas and decimals.

Thanks again. Worked perfect.

[Dan Korn]

Originally Posted by Rick J.

Is there anyway you could just explain what (/[^\d\.]/g, '') that all means. I know it's to strip all those out, but I'm very curious as to what the symbols mean. I don't see anything that tells it to remove the commas and decimals.

Yes, sorry, this deserves some explanation.

The JavaScript String.replace() function uses a shorthand called Regular Expressions.

Regular Expressions are a big topic, so I won't try to fully explain them here, but I can break down the call here:

var ttnum = StringToNumber(Field("amnt").replace(/[^\d\.]/g, ''));

So we're calling the String.replace function, on the String object returned by the Field function for the field "amnt". This gives us a string with a value such as "$1,234.56".

The String.replace() function takes two parameters, which, in this case, are a RegExp literal and a replacement string (which is '', or an empty string).

The RegExp literal /[^\d\.]/g breaks down like so:

/ - starts the RegExp literal
[ - starts a range/group (see Groups and Ranges)
^ - denotes that the range is a "negated" group
\d - denotes "any digit" (0-9) (see Character Classes)
\. - denotes a literal period/dot (decimal place) character
] - ends the range/group
/ - ends the RegExp literal
g - "global" flag, denoting to act on all matching instances

So [^\d\.] means "a group of characters including any digit and a period", negated with the caret ^, turning it into "any character that's NOT a digit or period." And the g flag says "ALL matches (instances) of any character that's not a digit or period."

So what this means is "match all instances of a character that is NOT a digit or a period/dot," and replace it with the replacement, which is '', i.e. an empty string, so it's replacing it with nothing, i.e. removing it.

Effectively, we're stripping out anything that's not a digit or a dot, including the dollar sign, any commas, and anything else, leaving just the digits and the decimal dot.

Therefore, a string such as "$1,234.56" gets turned into "1234.56", which can be parsed to the number 1234.56, which can then be used in mathematical calculations, such as multiplying it by two.

Now, sure, instead of using Regular Expressions, we could have written something like this:

var ttstr = Field("amnt");
var ttnumstr = "";
for (var i in ttstr)
{
    var c = ttstr[i];
    if ("1234567890.".indexOf(c) >= 0)
        ttnumstr += c;
}
var ttnum = StringToNumber(ttnumstr);

But this is much more succinct:

var ttnum = StringToNumber(Field("amnt").replace(/[^\d\.]/g, ''));

And, once you get the hang of Regular Expressions, you'll find them to be an extremely powerful tool.

[Rick J.]

Thanks Dan! That's an awesome explanation. It helps people like me to understand better, so I can do better in the future.