How to Use REGEX without VBA in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

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.


Use REGEX without VBA in Excel: 4 Easy Steps

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.

Prepare Data Set as An Easy Step to Use REGEX without VBA in Excel


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.

Setting Criteria Values for Letters and Numbers as An Easy Step to Use REGEX without VBA in Excel


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)))

Using an Combination as An Easy Step to Use REGEX without VBA in Excel

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


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.

Easy Steps to Use REGEX without VBA in Excel

Read More: How to Perform Pattern Matching in Excel


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.

Download Practice Workbook

You can download the free Excel workbook here and practice on your own.


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.


Related Articles


<< Go Back to RegEx in Excel | String Manipulation | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Araf Bin Jayed
Md. Araf Bin Jayed

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

2 Comments
  1. One of the clearest and easiest to follow Excel websites I have ever found. I am an advanced Excel user, and will probably use the VBA solution to extract alphanumeric or numeric strings, to isolate property numbers and generic building numbers, to try to link a GIS database and a non GIS database with what is meant to be the same data but cannot be verified.

    I really like this non VBA RegEx, I have just spent a bit of time recreating the example to learn from. You need to add a space before the first “Letters” reference. This threw me a bit as the formula was not working.

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Feb 25, 2024 at 11:25 AM

      Hi JAMES MARTIN,

      Thank you for your positive feedback and for taking the time to recreate the example from the article. I’m glad you found the non-VBA REGEX example useful for your learning. I rechecked the process, and it worked fine for me. Did you recreate the example on our provided Excel file or try it on another workbook? For our provided workbook, if you want to recreate another named range, then you might need another name or a space, as you mentioned. However, it’s always helpful to receive feedback. If you have any further questions or suggestions, feel free to let us know. Your input is appreciated!

      Regards
      Rafiul Hasan
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo