How to use the LIKE OPERATOR

The LIKE operator allows you to match a string to a pattern using Excel VBA. The image above demonstrates a macro that uses the LIKE operator to match a string to a given pattern.

The pattern is built on specific characters that I will demonstrate below.

1. Introduction

What are the syntax and arguments for the LIKE operator?

Excel VBA Function Syntax

result = string Like pattern

Arguments

result	Required. Any number.
string	Required. A string.
pattern	Required. A string that meets the required pattern characters described below.

What is VBA?

VBA (Visual Basic for Applications) is a programming language developed by Microsoft that is used for automating tasks in Microsoft Office applications such as Excel, Word, and Access. It allows users to create macros, automate repetitive tasks, and enhance Office functionality with custom scripts.

What is a User-Defined Function?

A User-Defined Function (UDF) is a custom function created by a user to perform specific calculations or tasks that built-in functions cannot handle. UDFs are commonly used in programming languages like VBA (Visual Basic for Applications) in Excel

• Users can define their own logic to meet specific needs.
• Once created a UDF can be used multiple times just like built-in functions.
• Accepts arguments and returns a result.
• Extends the capabilities of Excel or other applications.

How to enable developer tab?

Here is how to enable the "Developer" tab if it is missing on the ribbon:

1. Press with left mouse button on "File" located above the ribbon, a new pane appears.
2. Press with mouse on the "Options" button to access Excel settings.
3. Press with mouse on "Customize Ribbon" and then on the right side press with left mouse button on the checkbox next to the tab "Developer" to enable it.
4. Press with left mouse button on "OK" button to apply changes.

The "Developer" tab is now visible on the ribbon. Here are instructions for Excel 2007, Excel 2010 and Excel 2013

2. What characters can you use as a pattern in the LIKE operator?

The following characters are specifically designed to assist you in building a pattern:

Character	Desc	Text
?	question mark	Matches any single character.
*	asterisk	Matches zero or more characters.
#	number or hash sign	Any single digit.

A1A* - You can also use a string combined with the characters above to build a pattern. This matches a string beginning with A1A or is equal to A1A. The asterisk matches zero characters as well.

Characters	Desc	Text
[abc]	brackets	Characters enclosed in brackets allow you to match any single character in the string.
[!abc]	exclamation mark	The exclamation mark (!)  matches any single character not in the string.
[A-Z]	hyphen	The hyphen lets you specify a range of characters.

Back to top

2.1 How to make the LIKE operator case insensitive?

Add Option compare binary or Option compare text before any macros or custom functions in your code module to change how string evaluations are made.

The default setting is Option compare binary. Use Option compare text to make the comparison case-insensitive but put the code in a separate module so other macros/functions are not affected.

Setting	Desc
Option compare binary	Default.
Option compare text	Case-insensitive evaluations.

To learn more, read this article: Option Compare Statement

Back to top

2.2 What does the LIKE operator return?

The image above shows a macro in the Visual Basic Editor that returns either TRUE or FALSE depending on if the pattern matches the string or not.

Pattern *1* matches 552513256 and the macro above shows a message box containing value True.

result = string Like pattern

The LIKE operator returns a boolean value, TRUE or FALSE depending on if the pattern is a match or not. You can save the boolean value to a variable, the line above stores the boolean value in the variable result.

Back to top

3. Compare a cell value to a pattern

This simple User Defined Function (UDF) lets you specify a pattern and compare it to a cell value. If there is a match, the function returns TRUE. If not, FALSE. I am going to use this UDF in the examples below.

'Name User Defined Function
'Parameter c declared data type Range
'Parameter pttrn declared data type String
Function Compare(c As Range, pttrn As String) As Boolean

'Evaluate string in variable c with pattern saved to variable pttrn
'Return the result to the User Defined Function
Compare = c Like pttrn
End Function

Copy the code above and paste it to a code module in the VB Editor, if you want to use it. Where to put the code?

We are going to use this User Defined Function to compare patterns with strings located on a worksheet, read the next section.

Back to top

3.1 How to use the question mark (?) character

The picture above demonstrates the User Defined Function we created in section 2. It takes the string in column B and compares it to the pattern in column D. A boolean value True or False is returned to column E.

UDF syntax: Compare(string, pattern)

A question mark (?) matches any single character.

Formula in cell E6:

Value in cell B6 ABC matches A?C specified in cell D6, TRUE is returned to cell E6.

Formula in cell E7:

Value in cell B7 ABCD does not match pattern A?D. BC are two characters, a question mark matches any single character. FALSE is returned in cell E3.

Formula in cell E8:

Value in cell B8 ABCD matches the pattern specified in cell D8, ?BC?. TRUE is returned to cell E8.

Back to top

3.2 How to use the asterisk (*) character

The image above demonstrates the User Defined Function (UDF) described in section 2, it evaluates if a pattern matches a string using the LIKE operator. If so returns True. If not, False.

The UDF is entered in cell E9, E10, and E11. The first argument in the Compare UDF is a cell reference to the string and the second argument is a cell reference to the pattern.

Let's begin with the formula in cell E9:

The pattern tells you that the first three characters must be AAA and then the * (asterisk) matches zero or more characters. AAAC is a match to pattern AAA* and the UDF returns TRUE in cell E9.

Formula in cell E10:

aaa* does not match pattern AAAC. aaa is not equal to AAA. LIKE operator is case sensitive unless you change settings to Option Compare Text.

Formula in cell E11:

(*) matches zero or more characters, DDC23E matches DD*E.

Back to top

3.3 How to use the number sign or hashtag (#) character

The image above shows  patterns in column D and strings in column B, the hashtag character # matches a single digit. To match multiple digits use multiple #.

Formula in cell E3:

String 123 in cell B3 matches pattern 12#, TRUE is returned in cell E3.

Formula in cell E4:

String 123 in cell B4 does not match pattern 1# in cell D4, the hashtag character matches any single digit only.

Formula in cell E5:

String 123 in cell B5 matches the pattern in cell D5 #2#.

Back to top

3.4 Combining pattern characters

The following three examples use asterisks, question marks, and number signs combined.

Formula in cell E12:

Pattern *##?? in cell D12 matches string AA23BB in cell B12, the formula returns True in cell E12. The asterisk matches 0 (zero) to any number of characters, the hashtag matches any single digit.

Note that there are two hashtags in the pattern. The ? question mark matches any single character.

Formula in cell E13:

The string in cell B13 AA23BB does not match pattern *##? specified in cell D13. There must be one character after the digits, the string has two characters after the digits.

Formula in cell E14:

The string AA23BB in cell B14 matches the pattern in cell D14 *##*. The asterisk matches 0 (zero) to any number of characters, the hashtags match two single digits and the last pattern character is the asterisk.

Back to top

3.5 How to use brackets with the LIKE operator

Brackets match any single character you specify. A hyphen lets you compare a range of letters, however, they must be sorted from A to Z. [A-C] is a valid range but [C-A] is not valid.

Formula in cell E15:

The formula in cell E15 evaluates the string ABCD to pattern [A]* and returns TRUE. The first character in the string must be A or a, the number of remaining characters can be zero or any number in length.

Formula in cell E16:

The formula in cell E16 evaluates the string ABCD to pattern [A] and returns FALSE. The string must be only one character and that character must be A or a.

Formula in cell E17:

The formula in cell E17 compares the string ABCD to pattern [!A]* and returns FALSE. The first character in the string must be anything but character A and the number of remaining characters can be 0 (zero) or any number in length.

Formula in cell E18:

The formula in cell E18 compares the string C22R to pattern [A-Z]##? and returns TRUE. The first character in the string must be a letter between A to Z, then any two digits, and lastly a question mark that matches any single character.

Formula in cell E19:

The formula in cell E19 compares the string C22R to pattern [A-Z]##[A-Z] and returns TRUE. The string begins with any letter between A to Z, then any two digits, and lastly, any letter between A to Z.

Formula in cell E20:

The formula in cell E20 compares the string C222 to pattern [A-Z]##[A-Z] and returns FALSE. The string begins with any letter between A to Z, then any two digits, and lastly, any letter between A to Z. The string has a digit as the last character which isn't a match.

Back to top

4. Search for a regex pattern in column and get matching values (UDF)

The following user-defined function allows you to extract cell values using the LIKE operator and a pattern specified on the worksheet.

The formula is entered in cell D6 as an array formula, it returns multiple values to cells below if the pattern matches multiple values.

The example pattern used is in cell D3 "?B?". The question mark matches a single character, the UDF returns all values containing three letters and the middle letter is B.

Array formula in cell D6:D9:

How to enter an array formula

'Name User-defined Function
Function SearchPattern(c As Range, pttrn As String)

'Dimension variables and declare data types
Dim d as String

'Iterate through each cell in c
For Each cell In c

'Check if string matches pttrn, if so concatenate cell value and comma to variable d
If cell Like pttrn Then d = d & cell & ","

'Continue with next cell
Next cell

'Split string in variable d using comma character then transpose array and return values to UDF SearchPattern
SearchPattern = Application.Transpose(Split(d, ","))
End Function

Where to put the code?

Back to top

5. Search for a pattern and return values in an adjacent column (UDF)

This User Defined Function allows you to look for a pattern in a column and return the corresponding value in another column.

The UDF returns a value from column C if the corresponding value in column B on the same row matches the regex pattern specified in cell E3.

Array formula in cell E6:E9:

How to enter an array formula

'Name User Defined Function (UDF)
Function SearchCol(b As Range, c As Range, pttrn As String)

'Dimension variables and declare data types
Dim a As Long, d as String

'Count cells in cell range b
a = b.Cells.CountLarge

'For ... Next statement meaning iterate lines in between a times
For i = 1 To a

'Check if cell matches pattern, if so add c and a comma to variable d
If b.Cells(i) Like pttrn Then d = d & c.Cells(i) & ","
Next i

'Split values in variable d and return array to worksheet
SearchCol = Application.Transpose(Split(d, ","))
End Function

Where to put the code?

Back to top

6. Extract words that match a regex pattern from cell range (UDF)

The image above demonstrates a User Defined Function (UDF) that extracts words that match a given regex pattern. This means that the UDF may extract multiple words from the same cell.

You can use this UDF to extract phone numbers, zip codes, email addresses, html code, or pretty much anything from cells that contain a lot of data.

Array formula in cell D5:

If you want to split the data using a different character change the space character in the VBA code below to any delimiting character or characters.

6.1 How to enter an array formula

1. Select the cell range you want to use.
2. Press with left mouse button on in the formula bar.
3. Paste array formula to the formula bar.
4. Press and hold CTRL + SHIFT simultaneously.
5. Press Enter once.
6. Release all keys.

Your formula is now an array formula, you recognize array formulas by the beginning and ending curly brackets in the formula bar. {=array_formula}

Don't enter these characters yourself, they appear automatically.

6.2 VBA code

'Name User Defined Function (UDF)
Function ExtractWords(c As Range, pttrn As String)

'Iterate through each cell in cell range c
For Each cell In c

    'Split cell contents into an array using space as a delimiting character
    Arr = Split(cell, " ")

    'Iterate through each value in array Arr
    For Each a In Arr

        'Check if string in variable a matches pattern in variable pttrn
        If a Like pttrn Then

            'Add string a and a comma to variable d
            d = d & a & ","
        End If
    Next
    
Next cell

'Split variable d using comma as a delimiting character and return array to UDF
ExtractWords = Application.Transpose(Split(d, ","))

End Function

7. Where to put the code?

1. Press Alt+F11 to open the Visual Basic Editor (VBE).
2. Press with mouse on "Insert" on the top menu, see image above.
3. A popup menu appears. Press with left mouse button on "Module" to insert a module to your workbook.
4. Copy the VBA code.
5. Paste to the code window.
6. Return to Excel.

Did you know?

You can use the question (?)  mark and asterisk (*) characters in many Excel functions. The COUNTIF function in cell C2 demonstrated in the image above counts cells in cell range A2:A15 using the pattern in cell B2.

If you need to use even more complicated patterns Excel allows you to use regular expressions, see this thread:
How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

Back to top

Back to top

8. Count matching strings using regular expressions

This article demonstrates a User Defined Function (UDF) that counts how many times a string exists in a cell value based on a regex pattern.

Regex stands for regular expression, it is a pattern containing specific characters to search for substrings in strings. The patterns are at first glance hard to understand but with a little bit of practice very useful.

Imagine you want to extract phone numbers from a text file or a worksheet, however, the issue here is that there is also text combined with phone numbers and that makes it hard to filter out. Here a regular expression is coming to the rescue.

Looking for 555-412395 in a cell range is easy in excel but looking for phone numbers with 3 digits then a hyphen and then 6 more digits is a lot harder. Each digit can be anything between 0 and 9.

You will find a guide later in this post to most of the characters you can use in regular expressions and how to solve the problem with phone numbers.

8.1. What do I need to do to use regular expressions?

Nothing, you don't need to reference anything in VB Editor \ Tools for this to work. I will now demonstrate a simple UDF that counts matching strings in a cell range.

Back to top

8.2. User Defined Function - CountStringsCellRange

'Name User Defined Function (UDF) and parameters
Function CountStringsCellRange(c As Range, pttrn As String)

'Save value from cell range c to variable Rng
Rng = c.Value

'Save regex pattern to variable regexpattern
regexpattern = pttrn

'With ... End With statement
With CreateObject("vbscript.regexp")
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = regexpattern

'Check if number of cells in variable c is larger than 1
If c.Cells.CountLarge < 1 Then

    'For ... Next statement - based on the number of rows in array variable Rng 
    For rr = LBound(Rng, 1) To UBound(Rng, 1)

        'For ... Next statement - based on the number of columns in array variable Rng 
        For cc = LBound(Rng, 2) To UBound(Rng, 2)

            'Save found matches to Results
            Set Results = .Run(Rng(rr, cc))

            'Save the number of matches to variable i plus i
            i = i + Results.Count
        Next cc
    Next rr

    'Return number stored in variable i to worksheet
    CountStringsCellRange = i

'Go here if number of cells in variable c is smaller than or equal to 1
Else

    'Save found matches to Results
    Set Results = .Run(Rng)

    'Save the number of matches to variable i
    CountStringsCellRange = Results.Count
End If
End With
End Function

Back to top

8.3. Where to put the VBA code?

1. Copy UDF above.
2. Start/Open Excel.
3. Go to VB Editor (Alt+F11).
4. Press with left mouse button on "Insert" on the menu at the top.
5. Press with left mouse button on "Module".
6. Paste code to the code module.

Back to top

8.4. How do I start building regular expressions?

I will use the UDF above to show you how to work with patterns using specific characters. It is important to know which characters you can use and what they do.

8.4.1 Search is case sensitive

AbC - matches this exact string. A upper case letter A, a smaller case letter b and a upper case letter C

Back to top

8.4.2 A range of letters

A hyphen allows you to search for a range of characters or numbers. [] brackets allow you to search for exactly one character.

[A-Z] finds a single upper case letter between A to Z.

[A-Z][a-z] matches a single uppercase letter between A to Z and then a lower case letter between a to z.

Back to top

8.4.3 Match at least one or more characters

A+ matches a single A and multiple A's.

A+ matches A and AA and AAA but not B, three matches in total displayed in cell C9 in picture above.

Back to top

8.4.4 Match 0 or one characters before it

A? matches an empty string or one A.

Back to top

8.4.5 Match 0 or more characters

A* matches an empty string or one or more A.

Back to top

8.4.6 Match any character except newline

. matches any character

A.A matches AbA and ACA

Back to top

8.4.7 Match multiple characters

Curly brackets allow you to match multiple characters.

[A-Z]{4} is the same as [A-Z][A-Z][A-Z][A-Z] but easier to write. [A-Z]{4} matches 4 upper case letters between A-Z.

ABCD is the only match in string ABCD abcd aBcD to pattern [A-Z]{4}.

Back to top

8.4.8 NOT operator

^ allows you to exclude certain characters

[^A] matches B, C and D because they are not equal to A.

Back to top

8.4.9 OR operator

| is an OR operator.

A|C matches A and C in string ABCD.

Back to top

8.4.10 Escape characters

\ (backslash) escapes characters.

If you are looking for question marks in a string the backslash allows you to escape certain characters that would otherwise have different functionality.

Back to top

8.4.11 Match start and end of string

^ matches the start of a string.

$ matches the end of a string.

^ABC matches the three first characters ABC DCE

DCE$ matches the three last characters in string ABC DCE

BC$ does not match ABC DCE because it is not at the end of the string

Back to top

8.4.12 Group patterns

Parentheses allows you to group characters

W(AB){2} is the same as WABAB

Back to top

8.5. What about phone numbers?

In the beginning of this post I said that regular expressions are good for matching phone numbers.

The phone number I am looking for begins with three digits 0 to 9, the pattern becomes [0-9]{3}.

Next is a hyphen and then 6 more digits between 0 to 9, the pattern becomes -[0-9]{6} and combined [0-9]{3}-[0-9]{6}.

Back to top

9. Fetching values from ThingSpeak programmatically

Picture above shows cheap chinese IoT modules: a temp sensor and an esp8266-01 (25 mm x 15 mm)

Thingspeak is a web service that allows you to upload and store data from IoT devices. IoT stands for Internet of Things.

Today I want to demonstrate a User Defined Function (UDF) that gets data from ThingSpeak to Excel so you can easily analyze and chart the data yourself, in Excel.

The way this works is that using a simple http address with your channel and field number and parameters, you can easily fetch values from your feed.

Back to top

9.1. XML example

The HTTP address returns XML data, something like this:

Back to top

9.2. VBA code

The following UDF lets you specify a channel, field, and some more parameters. The function then removes the XML tags and returns data to the worksheet.

 

Remember to enter this as an array formula, 3 columns wide.

'Name function and define parameters
Function ThingSpeak(ch As Long, fld As Integer, prm As String)

'Add question mark if missing
If prm <> "" Then prm = "?" & prm

'Build url
url = "https://api.thingspeak.com/channels/" &ach & "/fields/" & fld & ".xml" & prm

'Enable error handling
On Error Resume Next

'Get data
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", url, False
http.Send
tmp = http.responseText

'Check for errors
If Err <> 0 Then

'Show message box
MsgBox "Error fetching values"

'Disable error handling
On Error GoTo 0

'Stop
Exit Function
End If

'Disable error handling
On Error GoTo 0

'Remove beginning xml data
tmp = Right(tmp, Len(tmp) - InStr(tmp, "") + 1)

'Clear xml tags with RemoveFromStr udf and then return values to worksheet
ThingSpeak = RemoveFromStr(tmp)

End Function

9.3. Remove XML tags

'Name User Defined Function and define parameters
Function RemoveFromStr(tmp As Variant)

'Dimension variables and declare data types
Dim tmp2 As Variant

'Clear xml tags using a regular expression
regexpattern = "<.*?&>"
With CreateObject("vbscript.regexp")
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = regexpattern
Set Results = .Run(tmp)
End With

'Check if there are matching strings
If Results.Count <> 0 Then

'With ... End With statement
With Results


'For ... Next statement
'Iterate through matching strings
For d = 0 To .Count - 1

'Replace matching string with a comma
tmp = Replace(tmp, .Item(d), ",")
Next
End With
End If

'Remove spaces
tmp = Replace(tmp, " ", "")

'Remove newline
tmp = Replace(tmp, vbLf, "")

'Replace T and Z with space character
tmp = Replace(tmp, "T", " ")
tmp = Replace(tmp, "Z", " ")

'Combine multiple commas
For i = 1 To Len(tmp)
If Mid(tmp, i, 1) = "," And Mid(tmp, i, 1) = Mid(tmp, i + 1, 1) Then
Else
tmp1 = tmp1 & Mid(tmp, i, 1)
End If
Next i

'Remove beginning and ending commas
If Left(tmp1, 1) = "," Then tmp1 = Right(tmp1, Len(tmp1) - 1)
If Right(tmp1, 1) = "," Then tmp1 = Left(tmp1, Len(tmp1) - 1)

'Split text into 1D array
tmp1 = Split(tmp1, ",")

'Count array
j = ((UBound(tmp1) - LBound(tmp1) + 1) / 3) - 1

'Build 2D array
ReDim tmp2(0 To j, 0 To 2)
ro = 0
co = 0
For i = LBound(tmp1) To UBound(tmp1)
tmp2(ro, co) = tmp1(i)
co = co + 1
If co = 3 Then
co = 0
ro = ro + 1
End If
Next i
'Return array to worksheet
RemoveFromStr = tmp2

End Function

Back to top

9.4. Where do I put the VBA code?

1. Open Visual Basic Editor. (shortcut keys Alt + F11).
2. Press with mouse on "Insert" on the top menu, a popup menu appears. See the above image.
3. Press with mouse on "Module" to insert a new module.
4. Copy VBA code.
5. Paste VBA code to code window, see image above.
6. Return to Excel.

Back to top

9.5 Instructions - how to use the UDF

9.6.1 User Defined Function - Syntax

ThingSpeak(c, fld, prm)

c - channel
fld - field
prm - parameter

9.6.2 How to enter the UDF as an array formula

1. Select cell range A1:C4.
2. Type =ThingSpeak(9,1,"results=4") in formula bar, see the image above.
3. Press and hold CTRL and SHIFT keys simultaneously.
4. Press Enter once.
5. Release all keys.
6. Excel automatically inserts curly brackets before and after the formula, see the formula bar in the image above.

Back to top

9.7. Extract XML using a formula - FILTERXML (Excel 2013)

The image above demonstrates how to extract data based on XML from ThingSpeak using the FILTERXML function. The column headers located in row 2 is used to extract the corresponding data from cell B9.

Formula in cell B3:

Back to top

Back to top

10. Extract cell references from a formula

This article demonstrates a User Defined Function that allows you to extract cell references from a formula in a given cell.

This is possible using a regular expression, a regular expression is a sequence of characters that define a search pattern, according to Wikipedia.

10.1. Extract cell references from a formula (User Defined Function)

This is the custom function I am using to extract cell references from a formula.

'Name User Defined Function (UDF) and specify parameter
Function ExtractCellRefs(c As Range) As String
'Webpage: https://www.get-digital-help.comhttps://www.get-digital-help.com/like-operator/#10
'Save pattern to variable regexpattern, change this before running the UDF
regexpattern = ""

'Evaluate regex pattern against range variable c
With CreateObject("vbscript.regexp")
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = regexpattern

'Save to variable results
Set Results = .Run(c.Formula)
End With

'Check if count is not equal to 0 (zero)
If Results.Count <> 0 Then

'With .. With End statement
With Results

'For ... Next statement
For d = 0 To .Count - 1

'Add item to variable Rstr
Rstr = Rstr & .Item(d) & ","
Next
End With

'Remove last character from string and return string to worksheet
ExtractCellRefs = Left(Rstr, Len(Rstr) - 1)

'Continue here if count is equal to zero
Else

'Return "No Matches" to worksheet
ExtractCellRefs = "No Matches"
End If

End Function

Back to top

10.2. Building the regular expression

The tricky part is the regular expression and I am a beginner at this, feel free to simplify my expression.

A cell reference can be anything from A1 to XFD1048576 so to match that the reg exp becomes:

Step 1 - Brackets match any specified character

You can define a range between A to Z using a hyphen.

[a-zA-Z] matches any letter from A to Z, both upper case and lower case.

Step 2 - Curly braces define the number of characters

[a-zA-Z]{1,3} matches 1 or up to 3 lower and upper case letters from A to Z. Example, XFD1048576 contains three letters.

Step 3 - Match a specified number of digits

[0-9]{1,7} matches 1 or more up to 7 digits from 0 to 9. Example, XFD1048576 contains 7 digits.

Back to top

10.3. Absolute and relative cell references - regex

A cell reference can also be absolute or relative or both and the $ sign tells which it is.

Step 1 - Dollar sign is a special character

The dollar sign is a special character in a regular expression, we need to use the backslash to make an expression that matches the dollar sign itself.

\ (backslash) escapes the character that follows

\$ allows us to use the character $, if I had not used the \ (backslash) $ (dollar sign) had been taken for a match at end of the string, I don't want that to happen.

Step 2 - Combine dollar sign with the regex pattern so far

\$? the question mark matches zero or one of the patterns defined before it, in this case, $ (dollar sign)

The picture below shows the matches for the above expression \$?[a-zA-Z]{1,3}\$?[0-9]{1,7}

It also works for this simple formula: =SUM(XFD1048576, $A$1,A$1,$A1), it returns these cell references: XFD1048576,$A$1,A$1,$A1

Back to top

10.4. Cell reference to a cell range

The above regular expression finds only cell refs to a single cell, a cell reference can also point to a cell range. How do we find a solution to that?

A cell ref to a single cell looks like this =A1, a cell ref to a cell range may look like this =A1:C3

The new part is

Step 1 - Parentheses creates a group

( (parentheses)  groups an expression

Step 2 - A colon is used if the cell reference points to a cell range

: a cell ref to a cell range contains a colon :

Step 3 - Combine parts

\$?[a-zA-Z]{1,3}\$?[0-9]{1,7} is the same as before, it matches letters and digits

(:\$?[a-zA-Z]{1,3}\$?[0-9]{1,7})  the parentheses groups the expression

Step 4 - Question mark matches the group

(:\$?[a-zA-Z]{1,3}\$?[0-9]{1,7})? the question mark matches zero or one of the pattern defined before it, in this case, the group.

Back to top

10.5. Cell references to other sheets

A cell reference to another sheet always ends with a ! (exclamation mark), the question mark matches zero or one of the patterns defined before it, in this case, the ! (exclamation mark)

This is a new group so I am leaving out the previous expression for now, I will add it later. The regular expression is now !?

The sheet name may have lower and upper letters from A to Z and also numbers 0 to 9, the regular expression is [a-zA-Z0-9]{1,99}!?

If there is a blank space in the sheet name Excel automatically surrounds the sheet name with two ' (apostrophe character), the expression becomes '?[a-zA-Z0-9]{1,99}'?!?

\s is any space character, this is what we have now '?[a-zA-Z0-9\s]{1,99}'?!?

'? the question mark matches zero or one of the patterns defined before it, in this case, the ' (apostrophe character)

Back to top

10.6. Cell references to other workbooks

There may also be cell references to other workbooks, it would be nice to find them as well.

A reference to a cell range in another workbook has the workbook name surrounded by these characters [].

('?[a-zA-Z0-9\s\[\]]{1,99})?'?!? , \ (backslash) escapes the character that follows in this case \[\]

The file name has a dot between the file name and the extension, ('?[a-zA-Z0-9\s\[\]\.]{1,99})?'?!?

The final expression is

Can it be made smaller?

Back to top

Final thoughts

I am sure there are characters allowed in a filename or sheet name that I have not considered in this post but I believe it will be easy to add those as well.

This does not take care of named ranges in a formula but it would not be hard to build a list of named ranges and then check if the formula contains named ranges.

Don't forget to use the regular expression in the UDF. Replace this line:

regexpattern = ""

with this:

regexpattern = "'?([a-zA-Z0-9\s\[\]\.])*'?!?\$?[a-zA-Z]+\$?[0-9]+(:\$?[a-zA-Z]+\$?[0-9]+)?"

 

Back to top

Regular expressions category

More than 1300 Excel formulas

Excel categories

Comments (1)