How to match alpha numeric cells to defined formats-CodePudding

I am trying to build a sheet to differentiate certain part numbers by vendor format.

ex.

FORMAT           VENDOR
----------    -------------
XXXXX-XXX     EXAMPLE 1
XX-XXXXX-XX   EXAMPLE 2
    
PN            VENDOR
--------      --------------
12345-123     FIND VENDOR
12-12345-12   FIND VENDOR

Basically, I want it to match the format of a pn cell and output the format that matches the vender..

any ideas? i know regex can do it but im having a hard time getting it too work. This is what I tried for the first example.

=REGEXMATCH(A6, "[{5}][-]\d{3}")

But it just outputs FALSE. How can I make it to work, where it will find the matching pattern?

CodePudding user response：

You can use REGREPLACE:

=XLOOKUP(REGEXREPLACE(A6,"\d","X"), A2:A3, B2:B3)

Here A2:A3 refers to the range with the patterns, and B2:B3 to the range with the vendor names that correspond to the patterns.

\d is the regular expression to match a digit. Each digit is replaced with an "X" so that you get a pattern. XLOOKUP will try to find the pattern and return the corresponding vendor.