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.
What's on this page
- Extract cell references from a formula (User Defined Function)
- Building the regular expression
- Absolute and relative cell references - regex
- Cell reference to a cell range - regex pattern
- Cell references to other worksheets - regex pattern
- Cell references to other workbooks - regex pattern
- Get the Excel File here
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.com/extract-cell-references-from-a-formula/
'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
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.
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
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.
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)
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?
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]+)?"
Regular expressions category
This article demonstrates a User Defined Function (UDF) that counts how many times a string exists in a cell value […]
The LIKE operator allows you to match a string to a pattern using Excel VBA. The image above demonstrates a […]
Picture above shows cheap chinese IoT modules: a temp sensor and an esp8266-01 (25 mm x 15 mm) Thingspeak […]
With end with statement category
Today I would like to share with you these small event handler procedures that make it easier for you to […]
Terry wants to make a different sized maze and I think that is a great idea. Perhaps you remember that I […]
Excel categories
4 Responses to “Extract cell references from a formula”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
I managed to simplify the regular expression to this:
'?([a-zA-Z0-9\s\[\]\.])*'?!?\$?[A-Z]+\$?[0-9]+(:\$?[A-Z]+\$?[0-9]+)?
Nice! This is exactly what I'm looking for.
I think the sheet name can contain additional characters though.
In theory, anything except \ / * ? : [ ]
OHHHH GOD! you really rock!! Thanks a lot for sharing
Hi thank you very much for this article!
Actually, I ran into a problem.
In a simple formula like: IF(YEAR($AJ5)<2023,"N205120042","N206070001")
the pattern enclosed in double quotes is also extracted.
Do you have any advice to avoid that please?
regards
Andrea