I am trying to build a regular expression that matches cell references in a formula.

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

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

Function ExtractCellRefs(c As Range) As String
regexpattern = ""
With CreateObject("vbscript.regexp")
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = regexpattern
Set Results = .Execute(c.Formula)
End With
If Results.Count <> 0 Then
With Results
For d = 0 To .Count - 1
Rstr = Rstr & .Item(d) & ","
Next
End With
ExtractCellRefs = Left(Rstr, Len(Rstr) - 1)
Else
ExtractCellRefs = "No Matches"
End If
End Function

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:

[A-Z]{1,3}[0-9]{1,7}

[A-Z] matches any upper case letter from A to Z.

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

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

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

\$?[A-Z]{1,3}\$?[0-9]{1,7}

\ (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 string, I don't want that to happen.

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

The picture below shows the matches for above expression \$?[A-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

Cell reference to a cell range

The above regular expression finds only cell refs to a single cell, 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

\$?[A-Z]{1,3}\$?[1-9]{1,7}(:\$?[A-Z]{1,3}\$?[1-9]{1,7})?

The new part is

(:\$?[A-Z]{1,3}\$?[0-9]{1,7})?

( (parentheses)  groups an expression

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

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

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

(:\$?[A-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

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 pattern 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 pattern defined before it, in this case the ' (apostrophe character)

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

('?[a-zA-Z0-9\s\[\]\.]{1,99})?'?!?\$?[A-Z]{1,3}\$?[0-9]{1,7}(:\$?[A-Z]{1,3}\$?[0-9]{1,7})?

Can it be made smaller?

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-Z]+\$?[0-9]+(:\$?[A-Z]+\$?[0-9]+)?"

 

Download excel *.xlsm file

Extract cell refs in formula.xlsm