cancel
Showing results for 
Search instead for 
Did you mean: 
Announcement | Alteryx Connect is now generally available! Find, understand, and trust all relevant information in your organization. Find out more.

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

SOLVED
Highlighted
florayaoyao
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 

  • Data Challenge
Quasar
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
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
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
Asteroid

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

 

Thank you so much. 

 

 

Flora