• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 62
  • Last Modified:

Regex expression

Hi There, how to write a regex expression to identify string match:
for ex: in my entire workbook it should highlight the summary column,  if it contains any string length greater than 6 digits or 8 digit. for ex: if summary column has 6 digit number it should highlight that row, if it has only 8 digit even then it should be highlighted, if it has both 6 and 8 digit it should get highlighted:

attached is the sample
Book1.xlsx
0
kausalya durgale
Asked:
kausalya durgale
  • 2
1 Solution
 
Bill PrewCommented:
There is no easy way in Excel to apply conditional formatting like you are describing without creating a VBA macro to do the checking of the cell content to see if it matches your pattern.  Would it be acceptable to create a VBA procedure that you could use to do this, or is adding VBA to your workbook not an option?


»bp
0
 
Bill PrewCommented:
To give you an idea of how that would work, I'm attaching a sample workbook with the highlighting done in conditional formatting.

The conditional formatting uses a formula, which in turn uses a user defined function to test a cell value against a regular expression.  The formula in the conditional formatting applied to all of column D was:

=TestRegex($D1, "\b(?:[0-9]{6}|[0-9]{8})\b")

Open in new window


And the user defined function is as follows:

Public Function TestRegex(strText As String, strPattern As String) As Boolean
    With CreateObject("Vbscript.Regexp")
        .Global = False
        .IgnoreCase = True
        .Pattern = strPattern
        TestRegex = .Test(strText)
    End With
End Function

Open in new window

EE29083452.xlsm


»bp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now