Hi There,
I have been trying to use formulae to remove currency symbols from a string.
Here is what I am trying to achieve
Data Input | Desired Output |
27,000.51 CAD | 27000.51 |
£28,000.51 | 28000.51 |
HK$29,000.51 | 29000.51 |
€30,000.51 | 30000.51 |
I am aware that I can use the Data Cleansing tool to remove the letters and blank space.
What I need is a formula/expression which removes:
1) The special characters from the start of the string. I would want the formula to say 'remove any special character, whatever it is/whatever they are, from the start of the string, to the left of the first number and to the right of the last number'
2) Removes the comma separators
What I have so far is this, but it requires me to define particular characters to remove:
Tonumber(replacechar(trim(regex_replace([Amount],"^-0{1,}",'-'),"0"),"£,$",""),1)
Can you point me in the right direction?
Thanks!
Rachel
Solved! Go to Solution.
Hey @RachRoberts
You can try: REGEX_Replace([ Data Input], "[^\d.]", "")
Which basically says, replace anything that isn't (^) a number or a fullstop.
Neil
That worked perfectly! Thank you VERY much for your help
Would that work if I want the following:
Have :///8.35.43
😕 8.5.44
:\\\8.4.45
\8.5.45
Would like the end result to be
8.35.43
8.5.44
8.4.45
and so on