Using Excel formulas to parse a substring from a cell value is usually difficult especially if the cell value or pattern is not easily defined. I have found that it requires various combinations of SEARCH, FIND, LEFT, RIGHT, MID, etc. The resulting formula is both unreadable and it does not successfully handle all cell values thus resulting in manual clean up. It would be nice if the SEARCH or FIND functions were able to use regular expressions by default.
The following VBA function allows you to parse a cell value based on a regular expression. To use this function, you must enable Microsoft VBScript Regular Expressions 5.5.
On the Developer ribbon, click Visual Basic
Once the Visual Basic window opens, go to Tools -> References…
From the References dialog box, check/enable Microsoft Regular Expressions 5.5
Click the OK button
Now that the regular expression reference is activated, insert a new module into the workbook if one doesn’t already exist. The code will not work if added directly to a worksheet object.
Add the below code to the module. You can then use it as part of formulas in the workbook, e.g. =RegExParse(A1,”ID[0-9]+”). This would parse the value in cell A1 returning the substring matching the pattern ID[0-9]+, e.g. ID0, ID123, ID183274917234.
The Legend of Zelda series has always been a favorite of mine. The original game eluded me until it was released as a retro game on the Wii’s Virtual Console. I played a rental copy from the local video store in the dark ages, but I was never able to get a copy of my own and the two day rental was not enough time to complete it. Thankfully, Santa brought me the gold cartridge of Zelda II: The Adventure of Link in 1988 and I was hooked. Since then, The Legend of Zelda: A Link to the Past and Ocarina of Time have become two of my favorite games of all time. While I love the stories, the puzzles, the dungeons, and the timeline that sort of makes sense if you don’t think too hard – it’s the dialogue between Link and the non-playable characters or the surrounding environment that really sets it apart from other favorites. I think the dialogue in earlier games was unintentionally humorous because of translations or poor writing, but the later games must know they’re funny.
I’m still very early into Breath of the Wild so I’m sure I’ll encounter more funny dialogue during the journey, but here are a few captures so far. Breath of the Wild is continuing the tradition and it’s an amazing game.
The Legend of Zelda: Breath of the Wild – Journal of Various WorriesThe Legend of Zelda: Breath of the Wild – Shouldn’t You Be Dead?The Legend of Zelda: Breath of the Wild – SHA-DINGThe Legend of Zelda: Breath of the Wild – Rumored Birthday
If you’re using Microsoft Excel to capture and track data, one of the challenges is maintaining good data quality when more than one person is updating the workbook. The data validation features in Excel help by allowing the user to select data based on pre-defined options in a list. This feature works well on individual cells. However, if you have a column that depends on the value in a different column, you will need to get a little more creative. This post describes the steps for creating cascading drop-down lists in Excel using a combination of data validation and named ranges.
Step 1 – Define the Reference Data
Let’s assume that you are collecting data that includes Organization and Department attributes. In this example, an Organization is the parent to one or more Departments. We’ll set up the reference data for the data validation drop-down lists as follows on its own worksheet.
Column A (“ORGANIZATION”) represents the list of valid Organizations that will appear in the Organization drop-down list.
Column B (“NAMED RANGE MAPPING”) represents the names given to ranges that will be defined in a later step.
Columns D through E (“ORG_X_DEPARTMENTS”) represent the lists of valid Departments that will appear in the Department drop-down list depending upon which Organization is selected.
Excel Cascading Drop-down List Reference Data
Step 2 – Define the Named Ranges
Now that we have the reference data established, we can define the named ranges that refer to these lists. The named range entry defined as ORGS_TO_DEPTS_MAPPINGS is the key to creating the cascading drop-down list functionality. It is important that the values in Column B match the names given to the ORG_X_DEPARTMENTS ranges. This value will be used in a subsequent step with an indirect and vlookup data validation formula.
Excel Cascading Drop-down List Reference Data Named Ranges
Step 3 – Define the Organization Data Validation (Parent Column)
In this step, we establish the standard data validation on the parent data in Column A (“ORGANIZATIONS”) on a new data collection worksheet.
Excel Cascading Drop-down List Top Level Validation
Step 4 – Define the Department Data Validation (Child / Dependent Column)
Here we establish the data validation rule that performs the cascading drop-down list function. Once the user selects a valid Organization, this formula performs a vlookup against the ORGS_TO_DEPTS_MAPPINGS named range and returns the name of the Department named range associated with the selected Organization. The indirect function then converts that name, a text value, into the range reference.
Excel Cascading Drop-down List Validation Formula
When you click the OK button, Excel will display the following error message. Click the Yes button to continue.
Excel Cascading Drop-down List Error Message
Step 5 – Test the Cascading Drop-down Lists
Now that the data validation rules are set up, we can test the cascading drop-down list functionality. Selecting “Org 1” will cause the validation drop-down list in the Department column to reflect only those Departments associated with “Org 1”. Selecting “Org 2” causes the Department drop-down list to show those Departments associated with “Org 2”.
Excel Cascading Drop-down List Example 1Excel Cascading Drop-down List Example 2
Step 6 – Macro to Validate Parent / Dependent Relationship
Now that the worksheet is functioning as expected, you release it for users to update and someone will inevitably enter data in a way that breaks the parent / dependent relationship. As an example, “Org 1” is selected in the Organization column and “Dept 1” is selected in the Department column. The user then returns to the Organization column and changes the entry to “Org 2”. The Department column retains the value of “Dept 1” which is not valid for the “Org 2” selection in the Organization column. To help avoid these errors, you can save the file as a macro enabled workbook and add the following code to the data entry worksheet. If this macro had been enabled, the “Dept 1” value would have been removed as soon as the user selected “Org 2”. This code assumes that the parent column is the first column (Column A) in the worksheet and the dependent column is immediately to the right (Column B). Please revise the code to meet your specific requirements.
