Md. Araf Bin Jayed
If you are using REGEX without VBA in Excel, this article may help you in this regard. REGEX is the abbreviated form of the term “regular expression.” It means you can check or see if all the data in your data set match a pre-existing pattern or not. In this article, I will show you how to use REGEX without VBA in Excel.
Download Practice Workbook
You can download the free Excel workbook here and practice on your own.
4 Easy Steps to Use REGEX without VBA in Excel
In this article, you will see four easy steps to use REGEX without VBA in Excel. As I am not going to use VBA in this procedure, I will need the help of some Excel functions to perform this task. So, to perform this task, go through each of the following steps.
Step 1: Prepare Data Set
The first step of this procedure will be preparing the data set. For that,
- Firstly, take the following data set with some patterns in column B.
- Here, I want to find results for the pattern that starts with three capital letters, then three numerical values, and finally ends with three small letters.
Step 2: Set Criteria Values for Letters and Numbers
In the second step, I will set the criteria values for letters and numbers. To do that, I will need two named ranges. You can create the ranges by
- First of all, go to the Formulas tab of the ribbon, and from the Defined Names group select Name Manager.
- Secondly, you will see the Name Manager dialog box, and from the box select New.
- Thirdly, the New Name wizard will pop up.
- In the Name type box, type Letters.
- Then, in the Refers to type box, insert the following pattern.
="abcdefghijklmnopqrstuvwxyz"- Lastly, click OK.
- Consequently, you will see the first defined range in the dialog box.
- Then, to add the second one again, click New.
- Afterward, like the previous New Name wizard, name this range as Numbers.
- Then, set the pattern as the following sequence.
="1234567890"- Lastly, press OK.
- Finally, after defining the two ranges, select the Close button from the dialog box.
Step 3: Use a Formula Combination
All the pre-requisitions to use the REGEX have been completed. Now, to see the result, I will need to apply a combination formula of several Excel functions. This formula will include the use of the AND, LEN, COUNT, FIND, MID, LEFT, ROW, INDIRECT, UPPER, and ISNUMBER functions. For doing that,
- Firstly, select cell C5 in the data set and insert the following formula.
=AND(LEN(B5)=9,COUNT(FIND(MID(LEFT(B5,3),ROW(INDIRECT("1:"&LEN(LEFT(B5,3)))),1),UPPER(Letters)))=LEN(LEFT(B5,3)),COUNT(FIND(MID(MID(B5,4,3),ROW(INDIRECT("1:"&LEN(MID(B5,4,3)))),1), Numbers))=LEN(MID(B5,4,3)),ISNUMBER(FIND(RIGHT(B5), Letters)))
Formula Breakdown
=AND(LEN(B5)=9,COUNT(FIND(MID(LEFT(B5,3),ROW(INDIRECT(“1:”&LEN(LEFT(B5,3)))),1),UPPER(Letters)))=LEN(LEFT(B5,3)),COUNT(FIND(MID(MID(B5,4,3),ROW(INDIRECT(“1:”&LEN(MID(B5,4,3)))),1), Numbers))=LEN(MID(B5,4,3)),ISNUMBER(FIND(RIGHT(B5), Letters)))
- The COUNT, FIND, MID, LEFT, ROW and INDIRECT functions of the first portion will inspect if the first 3 characters in cell B5 are uppercase letters or not.
- Then, the COUNT, FIND, MID, MID, ROW, INDIRECT, LEN and MID functions of the middle portion will check if the middle 3 characters of cell B5 are numeric digits
- Thirdly, the ISNUMBER, FIND and RIGHT functions will see if the last 3 characters are lowercase letters in cell B5.
- Fourthly, the LEN function will see if the total character length of the pattern of cell B5 is 9 or not.
- Finally, the AND function will return TRUE as the result if all of the above 4 conditions are true.
- Secondly, press Enter, and you will see the first pattern matches my criteria, and thus the formula is showing True.
Read More: How to Use REGEX to Match Patterns in Excel (6 Examples)
Step 4: Show Final Result
This is the final step of this procedure. Here, I will show the final result after performing all the above steps. To do that,
- First of all, place the cursor on the lower right corner of the first resultant cell and you will see the AutoFill feature.
- Then, drag the AutoFill to the lower cells to show the results of the other patterns as well.
- Finally, you will be able to see the desired results and can tell which pattern in your data set matches the predefined criteria.
Read More: How to Perform Pattern Matching in Excel (2 Suitable Methods)
Things to Remember
- While inserting the formula, remember to provide the correct cell reference.
- As there are multiple functions in the formula, remember to provide parenthesis in the correct order.
Conclusion
That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to use REGEX without VBA in Excel. Please share any further queries or recommendations with us in the comments section below.
The ExcelDemy team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions.
