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

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

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

I want the result to be:

2.5

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

Regex

Accepted answers

alexnajm

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

If you only want to keep numbers and decimals, try

[0-9]|\.

RBF

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!

RBF

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

All comments

alexnajm

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

If you only want to keep numbers and decimals, try

[0-9]|\.

RBF

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

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 😥

aatalai

@alexnajm very true, guess data celanese and then a formula tool with replace([field],"-","") assuming that's the only puncuatuion you would want to get rid of.

alexnajm

Agreed @aatalai!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703