cancel
Showing results for
Did you mean:

Remove special character from a string filed to create a number field. "<\$xxx.xx> "

SOLVED
Highlighted
Asteroid

Hello Alteryx Experts,

I am trying to remove some special character from a string field, that could allow me to change the type to a number field.

The original number looks like " <\$145,200.99>,  and I want to convert it to  145200.99.

I tried "Regex_replace" function also the tool of RegEx, but I am not familiar with how to use RegEx language at all... some one please advise.

Flora

Quasar

`ToNumber(ReplaceChar([String],"<>\$,",""))`

Any character in the second argument string "<>\$," with be replaced with an empty string, removing every occurrence of those characters.

Asteroid

Thank you Joe！

Actually, I just realize  this "<>" refers to negative balance.. . And also the formula gives me below conversion error....  Any idea?

Thank you,

Flora

ConvError: Formula (8): TONUMBER: 745.42 lost information in conversion
ConvError: Formula (8): TONUMBER: 14.32 lost information in conversion
ConvError: Formula (8): TONUMBER: 118.44 lost information in conversion
ConvError: Formula (8): TONUMBER: 62.21 lost information in conversion
ConvError: Formula (8): TONUMBER: 57.22 lost information in conversion
ConvError: Formula (8): TONUMBER: 750.00 lost information in conversion
ConvError: Formula (8): TONUMBER: 2690.71 lost information in conversion
ConvError: Formula (8): TONUMBER: 1029.60 lost information in conversion
ConvError: Formula (8): TONUMBER: 2669.97 lost information in conversion
ConvError: Formula (8): TONUMBER: 797.28 lost information in conversion
ConvError: Formula (8): TONUMBER: Conversion Error Limit Reached

Quasar

`ToNumber(ReplaceChar(ReplaceChar([String],"<","-")," >\$,",""))`

I am not able to recreate that error. Is the data coming out correct? If so we can use the ignore errors argument:

`ToNumber(ReplaceChar(ReplaceChar([String],"<","-")," >\$,",""),1)`

If it is not correct, can you provide a sample data source the causes the error?

Asteroid

now it is correct.. no error anymore.  :)

Thank you so much.

Flora