Converting strings to numbers when minus signs are on the right

I have a text file of invoiced and paid amounts that I'm manipulating and converting to an Excel file using Alteryx (which I'm new at). I'm having issues converting some of the fields from strings to numbers, in part because some of the numbers are negative and have a dash to the right (e.g. 5,371.58-). I've read some of the other posts on this sight, and tried using various combinations of "ToNumber" and "RegEx" formulas, but so far I've only succeeded in eliminating the negative sign, rather than converting the text string to a negative number. Any guidance would be appreciated.

Parse

Accepted answers

MarqueeCrew

Sorry,

IIF(Contains([Field],”-“),”-“+trim([Field],”-“),[Field])

Typo

You are using an in-line if statement

If-then-else-endif

If the string contains a - character,
Then it is - plus the original value without any leading or trailing- signs. Else if no - characters are found, just keep the original value.

I used the trim function in case a - sign is on the left side

Cheers,

Mark

All comments

MarqueeCrew

Try this:

IIF(Contains([Field],”-“),”-“+trim([Field{,”-“),[Field])

cheers,

Mark

dmag427

Thanks for the response. Unfortunately, I'm getting a "Malformed Function Call" error message related to the first field reference, which I'm struggling to address

MarqueeCrew

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15881

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703