How to use Regular Expressions (Regex) in Microsoft Excel both in-cell
and loops
itemprop="text">
How can I use regular expressions in
Excel and take advantage of Excel's powerful grid-like setup for data
manipulation?
- In-cell
function to return a matched pattern or replaced value in a
string.
- Sub to loop through a column of data and extract
matches to adjacent cells.
- What setup is
necessary?
- What are Excel's special characters for
Regular expressions?
/>
I understand Regex is not ideal for many
situations (To use or not to
use regular expressions?) since excel can use Left,
Mid, Right,
Instr type commands for similar manipulations.
style="font-weight: bold;">
Answer
href="http://en.wikipedia.org/wiki/Regular_expressions" rel="noreferrer">Regular
expressions are used for Pattern Matching.
To use in Excel follow these steps
:
Step 1: Add VBA
reference to "Microsoft VBScript Regular Expressions
5.5"
- Select "Developer"
tab ( rel="noreferrer">I don't have this tab what do I
do?)
- Select "Visual Basic" icon from
'Code' ribbon section
- In "Microsoft Visual Basic for
Applications" window select "Tools" from the top
menu.
- Select
"References"
- Check the box next to "Microsoft VBScript
Regular Expressions 5.5" to include in your
workbook.
- Click
"OK"
Step
2: Define your
pattern
Basic
definitions:
-
Range.
- E.g.
a-z matches an lower case letters from a to
z
- E.g.
0-5 matches any number
from 0 to
5
[]
Match exactly one of the objects inside these
brackets.
- E.g.
[a] matches the letter a
- E.g.
[abc] matches a single letter which can be a, b or
c
- E.g.
[a-z] matches any single
lower case letter of the
alphabet.
()
Groups different matches for return purposes. See examples
below.
{} Multiplier
for repeated copies of pattern defined before
it.
- E.g.
[a]{2} matches two consecutive lower case letter a:
aa
- E.g.
[a]{1,3} matches at least one and up to three lower case letter
a, aa, aaa
+
Match at least one, or more, of the pattern defined before
it.
- E.g.
a+ will match consecutive a's a,
aa, aaa, and so
on
?
Match zero or one of the pattern defined before
it.
- E.g. Pattern may or
may not be present but can only be matched one time.
- E.g.
[a-z]? matches empty string or any single lower case
letter.
*
Match zero or more of the pattern defined before it.
- E.g. Wildcard for
pattern that may or may not be present.
- E.g.
[a-z]* matches empty string or string of lower case
letters.
. Matches any
character except newline
\n
- E.g.
a. Matches a two character string starting with a and ending
with anything except
\n
|
OR operator
- E.g.
a|b means either a or
b can be matched.
- E.g.
red|white|orange matches exactly one of the
colors.
^
NOT operator
- E.g.
[^0-9] character can not contain a
number
- E.g.
[^aA] character can
not be lower case a or upper case
A
\
Escapes special character that follows (overrides above
behavior)
- E.g.
\., \\, \(,
\?, \$,
\^
/>
Anchoring
Patterns:
^
Match must occur at start of
string
- E.g.
^a First character must be lower case letter
a
- E.g.
^[0-9] First character must be a
number.
$
Match must occur at end of
string
- E.g.
a$ Last character must be lower case letter
a
/>
Precedence
table:
Order Name
Representation
1 Parentheses ( )
2 Multipliers ? + *
{m,n} {m, n}?
3 Sequence & Anchors abc ^ $
4 Alternation
|
/>
Predefined Character
Abbreviations:
abr
same as meaning
\d [0-9] Any single digit
\D [^0-9] Any single
character that's not a digit
\w [a-zA-Z0-9_] Any word character
\W
[^a-zA-Z0-9_] Any non-word character
\s [ \r\t\n\f] Any space
character
\S [^ \r\t\n\f] Any non-space character
\n [\n] New
line
/>
Example 1:
Run as macro
The following
example macro looks at the value in cell A1 to see if the first
1 or 2 characters are digits. If so, they are removed and the rest of the string is
displayed. If not, then a box appears telling you that no match is found. Cell
A1 values of 12abc will return
abc, value of 1abc will return
abc, value of abc123 will return "Not
Matched" because the digits were not at the start of the
string.
Private Sub
simpleRegex()
Dim strPattern As String: strPattern = "^[0-9]{1,2}"
Dim strReplace As String: strReplace = ""
Dim regEx As New
RegExp
Dim strInput As String
Dim Myrange As
Range
Set Myrange = ActiveSheet.Range("A1")
If strPattern <> "" Then
strInput = Myrange.Value
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = strPattern
End
With
If regEx.Test(strInput) Then
MsgBox
(regEx.Replace(strInput, strReplace))
Else
MsgBox ("Not
matched")
End If
End If
End
Sub
/>
Example 2:
Run as an in-cell
function
This example is the same as
example 1 but is setup to run as an in-cell function. To use, change the code to
this:
Function
simpleCellRegex(Myrange As Range) As String
Dim regEx As New
RegExp
Dim strPattern As String
Dim strInput As String
Dim strReplace As String
Dim strOutput As
String
strPattern =
"^[0-9]{1,3}"
If strPattern <> "" Then
strInput = Myrange.Value
strReplace = ""
With
regEx
.Global = True
.MultiLine = True
.IgnoreCase =
False
.Pattern = strPattern
End
With
If regEx.test(strInput) Then
simpleCellRegex = regEx.Replace(strInput, strReplace)
Else
simpleCellRegex = "Not matched"
End If
End If
End
Function
Place
your strings ("12abc") in cell A1. Enter this formula
=simpleCellRegex(A1) in cell B1 and
the result will be "abc".
src="https://i.stack.imgur.com/q3RRC.png" alt="enter image description
here">
/>
Example 3:
Loop Through Range
This
example is the same as example 1 but loops through a range of
cells.
Private Sub
simpleRegex()
Dim strPattern As String: strPattern = "^[0-9]{1,2}"
Dim strReplace As String: strReplace = ""
Dim regEx As New RegExp
Dim strInput As String
Dim Myrange As Range
Set Myrange
= ActiveSheet.Range("A1:A5")
For Each cell In
Myrange
If strPattern <> "" Then
strInput =
cell.Value
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern =
strPattern
End With
If regEx.Test(strInput)
Then
MsgBox (regEx.Replace(strInput, strReplace))
Else
MsgBox ("Not matched")
End If
End If
Next
End
Sub
/>
Example 4: Splitting
apart different patterns
This example loops
through a range (A1, A2 &
A3) and looks for a string starting with three digits followed
by a single alpha character and then 4 numeric digits. The output splits apart the
pattern matches into adjacent cells by using the ().
$1 represents the first pattern matched within the first set of
().
Private
Sub splitUpRegexPattern()
Dim regEx As New RegExp
Dim strPattern
As String
Dim strInput As String
Dim Myrange As
Range
Set Myrange =
ActiveSheet.Range("A1:A3")
For Each C In Myrange
strPattern = "(^[0-9]{3})([a-zA-Z])([0-9]{4})"
If strPattern
<> "" Then
strInput = C.Value
With
regEx
.Global = True
.MultiLine = True
.IgnoreCase =
False
.Pattern = strPattern
End With
If
regEx.test(strInput) Then
C.Offset(0, 1) = regEx.Replace(strInput,
"$1")
C.Offset(0, 2) = regEx.Replace(strInput, "$2")
C.Offset(0, 3) = regEx.Replace(strInput, "$3")
Else
C.Offset(0, 1)
= "(Not matched)"
End If
End If
Next
End
Sub
Results:
src="https://i.stack.imgur.com/9eCZ5.png" alt="enter image description
here">
/>
Additional Pattern
Examples
String
Regex Pattern Explanation
a1aaa [a-zA-Z][0-9][a-zA-Z]{3} Single alpha, single
digit, three alpha characters
a1aaa [a-zA-Z]?[0-9][a-zA-Z]{3} May or may not
have preceeding alpha character
a1aaa [a-zA-Z][0-9][a-zA-Z]{0,3}
Single alpha, single digit, 0 to 3 alpha characters
a1aaa
[a-zA-Z][0-9][a-zA-Z]* Single alpha, single digit, followed by any number of alpha
characters
\<\/[a-zA-Z][0-9]\> Exact non-word
character except any single alpha followed by any single
digit
No comments:
Post a Comment