How to cast Text type financial numbers that use parentheses to valid Number type numbers

Say your source data of financial numbers is Text type. This means when they have negative values, they will be enclosed in parentheses. These parentheses prevent a direct cast to Number type as such values get evaluated to Null rather than to a negative number.

We can use a formula that will detect the presence of the parentheses, append a minus sign and then remove the parentheses prior to the casting to Number type.


If(Contains([Financial Numbers (Text Type)], "("), Number(Concat("-", Replace(Replace([Financial Numbers (Text Type)], ")", ""), "(", ""))), Number([Financial Numbers (Text Type)]))

1 Like

This also works:
Number(RegexpReplace([Financial Numbers (Text Type)], "\\((\\d+)\\)", "-\\1"))