1

I have to check if the content of a cell matchs with a specific format.

If it is the case, then I have to put Yes else No in the Result column.

Format is the following one : REFXXXX(,REFXXXX,...).

Content into bracket is optional and X should be a sequence of number (minimum 8, maximum 12).

Data example

Reference                              | Result
REF12324567                            | Yes
REF1                                   | No
                                       | No
Some text                              | No
REF1234567,REF789654123,REF741258963   | Yes

With a regex, it can be REF(\d{8,12})((,REF(\d{8,12}))*)

However I don't know if it is possible to use a regex as Excel formula.

Can you please explain me how to do?

Thank you, Regards

Royce
  • 1,557
  • 5
  • 19
  • 44

1 Answers1

2

There is no direct equivalent being a worksheetfunction. You could however use FILTERXML() as some sort of replacement feature to "split" a string into different elements and use some xpath to validate each element.

enter image description here

Formula in B1:

=IF(AND(ISERROR(FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s[not(starts-with(., 'REF') and string-length() >=11 and string-length() <=15 and translate(., 'REF', '')*0=0)]")),A1<>""),"Yes","No")
  • "<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>" - Create a valid xml-string.
  • //s - Return s-nodes where:
    • not(starts-with(., 'REF') - s-nodes do not start with "REF", and;
    • string-length() >=11 and string-length() <=15 - A string is of a total lenght between 11-15, and;
    • translate(., 'REF', '')*0=0 - If "REF" is removed the remainder is numeric.

If there is no s-nodes that can be returned after this validation FILTERXML() will throw an error which we catch with ISERROR() nested inside an IF() to return "Yes" or "No".

  • Note that FILTERXML() is case-sensitive.
  • Note that your 4th sample does not validate.
  • Note that you could also look at building your own UDF (User Defined Function) through the use of VBA and the regex-object. This way you can use regular expressions as you know it. For information on that subject, see this great source.

If you wish to know more about the use of FILTERXML() I'd like to redirect you to: Excel - Extract substring(s) from string using FILTERXML

JvdV
  • 70,606
  • 8
  • 39
  • 70