remove non numeric characters from the beginning or end of a string (not the middle)

[RBF]

Hi!  

Regex question.  How can I remove non numeric characters from the beginning or end of a string (but not from the middle)?

e.g. before:

65x

2.5

si98

a99a

3-

I want the result to be:

65

2.5

98

99

3

This is important because I do not want to lose the decimals, e.g. 2.5 should not become 25

[alexnajm · Accepted solution]

You can use Regex here to keep only numbers (and periods for that matter since you have decimals)! You’d use something along the lines of \d+\.?\d+

try Regex_Replace([Column], “.*(\d+\.?\d+).*”, “$1”) in a Formula for example! I can test once in front of a computer

[ChrisTX · Accepted solution]

If you only want to keep numbers and decimals, try

[0-9]|\.

[RBF · Accepted solution]

Thank you both!  I think I have managed a sort of inelegant workaround with:

REGEX_Replace([Yrly Volume], "[^0-9.]", "")

but then I also had to do a follow up formula:

if [Yrly Volume] = "." then 0 else [Yrly Volume] endif

because someone has entered a single full stop in one of the records!

[alexnajm]

That looks like it would work - nice solution @RBF !

Hoping my answer gave at least some inspiration :)

[RBF · Accepted solution]

Yes, indeed you both helped!  Sometimes you just need someone to help you think through a problem!   :) 

[alexnajm]

Awesome! Can you mark our responses as solutions then? You can even mark your own answer :)

[alexnajm]

@RBF including ours if you don't mind! Thank you in advance

[aatalai]

@RBF you could use the data cleanse tool and select remove letters

[alexnajm]

@aatalai there are non-letter instances like "-" to be mindful of! And we can't select the cleanse punctuation in that tool because then it would remove the decimal too 😥