Regex to detect string is x.x.x where x is a digit from 1-3 digits

codelover 0
Viewed 33

I have values 1000+ rows with variable values entered as below

5.99
5.188.0
v5.33
v.440.0

I am looking in Gsheet another column to perform following operations:

  1. Remove the 'v' character from the values
  2. if there is 2nd '.' missing as so string can become 5.88 --> 5.88.0

Can help please in the regex and replace logic as tried this but new to regex making. Thanks for the help given

=regexmatch(<cellvalue>,"^[0-9]{1}\.[0-9]{1,3}\.[0-9]{1,3}$")

I have done till finding the value as 5.88.0 returns TRUE and 5.99 returns false, need to now append ".0" so 5.99 --> 5.99.0 and remove 'v' if found.

regex
1 Answers

You can use a combination of functions, it may not be pretty, but it does the work

  • Replace any instance of v with an empty string using substitute, by making the content of the cell upper case, if we don't put UPPER(CELL) we could exclude any upper case V or lower case v(it will depend which function you use)

SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])

=SUBSTITUTE(UPPER(A1),"V","")

enter image description here

  • Look for cell missing the last block .xxx, you need to update a bit your regex to specified that the last group it's not present

^([0-9]{1}\.[0-9]{1,3} ( \.[0-9]{1,3}){0} )$

REGEXMATCH(text, regular_expression)

CONCATENATE(string1, [string2, ...])

=IF(REGEXMATCH(substitute(upper(A2),"V",""),"^([0-9]{1}\.[0-9]{1,3}(\.[0-9]{1,3}){0})$"),concatenate(A2,".0"), A2)

enter image description here

  • The last A2 will be replace with something similar than what we have until now, but before that we need to make small change in the regex, we want to look for the groups you specified were the last group it's present, that's your orignal regex, if it meets the regex it will put it in the cell, otherwise it will put INVALID, you can change that to anything you want it to be

^([0-9]{1}.[0-9]{1,3}.[0-9]{1,3})$

This it's the piece we are putting instead of the last A2

IF(REGEXMATCH(substitute(upper(A2),"V",""),"^([0-9]{1}\.[0-9]{1,3}\.[0-9]{1,3})$"),substitute(upper(A2),"V",""),"INVALID")

With this the final code to put in your cell will be:

=IF(REGEXMATCH(substitute(upper(A2),"V",""),"^([0-9]{1}\.[0-9]{1,3}(\.[0-9]{1,3}){0})$"),concatenate(A2,".0"),IF(REGEXMATCH(substitute(upper(A2),"V",""),"^([0-9]{1}\.[0-9]{1,3}\.[0-9]{1,3})$"),substitute(upper(A2),"V",""),"INVALID"))

enter image description here

userALF40
Related