Forum Discussion
Hussein_Mohamed
Oct 29, 2024Brass Contributor
excel assist
Dears,
could you please assist regarding the below sample of data to extract y4-02-cb-232 for the first row by using regex match and xlookup.
حافز ****** الوحدة y4-02-cb-232 - **** عم*****ى يوسف |
حافز ** الوحدة Y4-05-CD-122 - على * ** ** ** * |
حافز * الوحدة Y5-10-CO-222 -احمد **** *** * ** |
حافز بيع *** Y4-06-CD-121 - محمد ****** *** |
Thanks in advance
- peiyezhuBronze Contributor[yY]\d-\d{2}-\w{2}-\d{3}
- smylbugti222gmailcomIron ContributorI can assist you in extracting the desired information using regex match and XLOOKUP functions.
To extract 'y4-02-cb-232' from the first row, you can use the following methods:
*Regex Match:*
1. Use the regex pattern: `\b(Y4-02-CB-232)\b`
2. Apply this pattern using the regex extract function, such as: `=REGEXEXTRACT(A1, "\b(Y4-02-CB-232)\b")` (assuming data is in cell A1)
*XLOOKUP:*
1. Use the XLOOKUP function with the following syntax: `=XLOOKUP("y4-02-cb-232", A:A, 1, FALSE)`
2. Assuming data is in column A, this formula will search for the exact match and return the corresponding value.
*Alternative Approach:*
1. Use the FILTER function: `=FILTER(A:A, REGEXMATCH(A:A, "y4-02-cb-232"))`
2. This will return the entire row where the value matches.For the XLOOKUP regex match works
i.e.
=XLOOKUP("(?xi) y\d-\d{2}-\w{2}-\d{3}",C2:C5,D2#,,3)