Converting String to Number

I have two fields that are set as a string however we use them to input currency numbers into. (Example: $1,000.00 or ($2,000.00)). I tried using the ToNumber() function however that is not working. I'm assuming I can use the regex tool however I'm really not formula with that. Any suggestions would be great. Thanks Heather

Preparation

Accepted answers

afv2688

Hello @hholland,

Try using this:

REGEX_Replace([Field1], '[$|\)\,]', '')

ToNumber(REGEX_Replace([Classification Adj], '[\(]', '-'))

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

Collections - Loan Provision - Amt Issue.yxmd

All comments

NickSm

Hey @hholland

The most straightforward way to change the data type of a field would be to use a Select tool, and changing the data type to any of the numeric options in the drop-down list.

Though when working with currencies, the '$' and comma characters will always cause the field to revert to a string, so you may have to trim or remove those before trying to do any calculations with that field. Then if your desired output is to be formatted as currency, you can transform your data back as the last step before output.

hholland

NickSm, Thanks for the response. I can't use trim as the same column could have $1,000 or ($1,000) so they have a different number of characters in front to be trimmed. My end result needs to be 1000.00 or -2000.00 so that it's an actual number that I can add to another amount field. I need to have the dollar sign, comma and parenthesis removed and leave the period so the number doesn't get change (example if period was removed: from $1,000.00 to 100000). This is why I assumed I would need to use the RegX tool, however I don't really know much about the tool. Thanks

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335