6 Replies Latest reply: May 15, 2014 10:07 AM by karen123 _

# character vs numeric fields

i am still a novice with Monarch. Thanks for your patience.

In the column of my report there are positive and negative numbers both preceeded by leading zeros.  If i change the field properties to number the negative amounts are shown as 'null'.  If i change the field properties to character any negative numbers do not convert properly into Excel. is there a function or formula i can use on the entire column to remove any leading zeros and have all read as numbers? Thanks

• ###### character vs numeric fields

Are you sure you have your trap defined correctly? I just created a simple text file:

-000001

-000123

0003456

0000002

And was able to properly define it as a Numeric trap. Monarch converted the numbers correctly.

Can you provide a sample - scrubbing any sensitive data?

Thanks.

• ###### character vs numeric fields

0000240.00

0000124.03

0000150.00

0000076.00

0000116.00

0000026.00

000-150.00

0000-76.00

000-116.00

0000-26.00

0000000.00

Here's a sample from the report. My minus sign is in the middle of the field.  Thanks.

• ###### character vs numeric fields

Ah, OK.

This off the top of my head, so there may be a better way:

Trap it as Character. Then make a calculated field as numeric and use this formula:

VAL(If(instr("-",field)=0,field,substr(field,instr("-",field),len(field))))[/b]

Where field is the name of your character field.

This searches for the minus sign within your field. It it doesn't find it, then just return the field back. If it does find it, it returns the substring of your field starting at the point where it found the minus sign. Either string is passed back to the VAL() function to convert it into a number.

EDIT: After actually toying with it in Monarch, I've come up with this alternate formula:

Val(substr(field,max(1,instr("-",field))))[/b]

instr() will return the position of the minus sign if found or 0 if not. Since Substr() can't use 0 as a starting point we use MAX() with 1 so that if it's 0, it will return 1. You don't really need the last part of Substr() Monarch will just return the correct length of the field.

• ###### character vs numeric fields

This is equivalent in functionality, but might be a bit more readable for a beginner:

/* if negative signs exist in the field */

if(InStr("-",A)>0,

/* strip out the negative sign, convert it to a number, and then multiply that by -1 */

Val(Strip(A,"-"))*-1,

/*otherwise just convert it to a number */

Val(A))

/CODE

Of course, Nick's formula works perfectly well too.

• ###### character vs numeric fields

Thanks Data! My background is programming, so I try to find the path of least resistance. In other words, I don't always tailor my response to the audience at hand. My bad!

• ###### character vs numeric fields

Thank you very much. It worked perfectly. You are the greatest. Did i mention that the report is 2.5 million lines? You saved me many many hours.