STRING TO NUMERIC CONVERSION

[dunkindonut7777]

Hi I would like to know if how can I convert the string value with parenthesis character to numeric value. I tried to change the data type using select toll but it seems it is not working. 

Field (string)	Expected string (numeric)
(19090.56)	-19090.56
(7324940)	-7324940

How can I convert it without error? Thanks

[Matthew]

i'd use a formula tool to remove the parenthesis, and multiply by -1

if contains([Field],'(') then
  ToNumber(Replace(Replace([Field], ')', ''), '(', ''))*-1
else
  ToNumber([Field])
endif

[DawnDuong]

hi @dunkindonut7777 

Unfortunately to convert the accounting value format with the () to numeric will require some regex, as far as I am aware, it is not doable via a simple Tonumber (check it out here: https://help.alteryx.com/20213/designer/conversion-functions)

REGEX_Replace (https://help.alteryx.com/designer-cloud/string-functions) probably is the most efficient in this case.

Something like 

REGEX_Replace([Field], "(\d+\.\d+)", "-\d+\.\d+",1)

This should work when you have both positive and negative numbers by adding an if expression to handle.

Dawn

[Maskell_Rascal · Accepted solution]

Hi @dunkindonut7777 

Add this formula right before the Select tool. 

TrimRight(Replace([Field (string)], '(', '-'),')')

This replaces removes the brackets and replaces them with a dash prior to converting to a double. 

Let me know if this works for you.

Cheers!

Phil 

[atcodedog05]

Hi @dunkindonut7777 

Here is how you can do it.

Workflow:

Hope this helps : )

[dunkindonut7777]

it works thank you for this 🙂