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.
Thank you in advance!
Flora
Solved! Go to Solution.
How about:
ToNumber(ReplaceChar([String],"<>$,",""))
Any character in the second argument string "<>$," with be replaced with an empty string, removing every occurrence of those characters.
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
How about this to convert < to negative:
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?
now it is correct.. no error anymore. :)
Thank you so much.
Flora