RegEx - Digit Dot Digit

[knnwndlm]

Hello,

I have a set of records that has the following string format:

321.210

123.xxx

125.32x

223.1xx

243.3xx

654.353

783.55x

688.xxx

783.TDX

628.Txx

I would like to remove anything with a letter and replace it with zeroes unless if all zeroes after the dot, then remove the dot and only capture the first three digits.

Here's what I want to capture from the above:

321.210

123

125.320

223.100

243.300

654.353

783.550

688

783

628

Could somebody help me with the RegEx expression to do this?

Thanks,

knnwndlm

[binuacs]

@knnwndlm One way of doing ths

[knnwndlm]

@binuacs 

Thank you for the quick reply.  How do I revise the RegEx to remove all zeroes from the right with anything beyond the first 7 digits to be removed?  In other words, 125.32x becomes 125.32 and 243.3x becomes 243.3.  Just found out that my column to convert the digits over also have more than 7 characters.  It could run as long 10-20 characters after the period.

I was able to do this the Left and TrimRight formulas.  Just want to see how to do this with RegEx.

Thanks,

kwl

[knnwndlm]

@binuacs 

Could you please help me under the RegEx as I'm still learning it? 

Thanks,

kwl

[binuacs · Accepted solution]

@knnwndlm I am not following your second requirement of about 7 digits in length, can you provide a sample input file and expected output result

regarding the regex

REGEX_Replace([Data], '[^\d\.]', '0') - Removes all the non-digits except the '.' from the given data

 it will read as if the given string is not digit or . then remove all to 0, 

the second formula

REGEX_Replace([output], '(.*)\.(.*)', '$2') - gives two groups, the first group before the decimal part and the second group after the decimal part

eg: output = 123.00, then the above formula groups it into (123).(000)

in this case, we just need to check whether the second part is all zeros then ignore it, thats what the below formula does

IIF(toNumber(REGEX_Replace([output], '(.*)\.(.*)', '$2'))=0 - checks the second part is zero

,REGEX_Replace([output], '(.*)\.(.*)', '$1') - if yes then only consider the first part ie. $1 - means first group

,[output])

[apathetichell]

I think the poster is saying that they don't care about non zeros after the thousandth place (ie if the string is 123.asdfjkl5809808580 - it would be come 123 

if this is accurate - just change the first expression from using [output] to left([output],7) to capture the first 7 characters on the left. in regex ^.{7} would be the first 7 characters - but to build a regex check for that - when you could use the left() function would be overkill.

[msreddy8951]

Find my solution Below and let me know.

Up to here am reaching.

Reg Ex:  ^(\d+\.\d{0,3})\D*$