Remove all special characters from the start and end of a string

[RachRoberts]

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

[LordNeilLord · Accepted 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

[RachRoberts]

That worked perfectly! Thank you VERY much for your help

[Kim_Donner]

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