Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

florayaoyao
8 - 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.   

 

Thank you in advance! 

Flora 

4 REPLIES 4
Joe_Mako
12 - Quasar

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.

 

florayaoyao
8 - 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

Joe_Mako
12 - Quasar

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?

florayaoyao
8 - Asteroid

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

 

Thank you so much. 

 

 

Flora 

Labels