Excel / Possible Regex help
Excel / Possible Regex help
Excel / Possible Regex help
Excel / Possible Regex help
Excel / Possible Regex help
Excel / Possible Regex help Excel / Possible Regex help Excel / Possible Regex help Excel / Possible Regex help Excel / Possible Regex help Excel / Possible Regex help Excel / Possible Regex help Excel / Possible Regex help
Excel / Possible Regex help Excel / Possible Regex help
Excel / Possible Regex help
Go Back  Xtreme Visual Basic Talk > > > Excel / Possible Regex help


Reply
 
Thread Tools Display Modes
  #1  
Old 02-09-2009, 11:17 PM
adamwitt adamwitt is offline
Newcomer
 
Join Date: Feb 2009
Posts: 1
Default Excel / Possible Regex help


Hi there.

I am trying to test certain cell values to see if they contain subtrings of a certain format at the END of the cell sting value.

E.G.
I have cells with the values...

324f5
346-ssth
4565-Q1-09
dsfsd46
454354-FEB2-09
546-5-MAR03-09
234235
34534678
23333
5999


I want to run a macro that searches through the cells and tests to see if they have a substring sitting at the end of the value that is in the format "-Q[number]-[number][number]" OR "-[3 LETTER MONTH ABBREVIATION][number]-[number][number]" OR "-[3 LETTER MONTH ABBREVIATION][number][number]-[number][number]"
and then if the test is true, the cell color is changed to RED.

So if I ran the macro on the example I gave above, it would turn the cells with the following values red:
4565-Q1-09
454354-FEB2-09
546-5-MAR03-09


I can do all the VB side of things but I don't know Regex but know it would help. I'm in a bit of a rush to finish this job for work because I finish working here in 2 days and need to get it done before I leave! Could anyone please help me with the regex? thanks!!
Reply With Quote
  #2  
Old 02-10-2009, 06:56 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hi Adam,

Regular Expressions are almost impossible to do without a tool to help you test the various expressions.

There are many free versions out there. One which I recommend is
Expresso - A Tool for Building and Testing Regular Expressions (CodeProject)
.

I would also recommend reading the The 30 Minute Regex Tutorial (CodeProject). It really is a 30 minute read (or less) and is a great introduction into the basics of RegEx.

As for your specific codes, I'm not sure that I understand completely, so I think you should read the tutorial, download Expresso, and then take a stab at it yourself.

But to help get you going, it looks like you want to trap the following two basic cases:

Code:
(1) [Any Number of Digits]-Q[Digit]-[Digit][Digit]
(2) [Any Number of Digits]-[3 Letter Month Code][One or 2 Digits]-[Digit][Digit]
To trap the first one, that is, to trap examples such as "4565-Q1-09" I think you want:

Code:
\d*-Q\d-\d\d
To trap the 2nd one, that is, to trap examples such as "454354-FEB2-09" or "546-5-MAR03-09", I think you'd want:

Code:
\d*-(JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC)\d{1,2}-\d\d
But I would definitely test these within Expresso first to make sure that it does what you want...

Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Mike Rosenblum; 02-10-2009 at 07:02 AM.
Reply With Quote
  #3  
Old 03-09-2009, 03:45 PM
Cylian's Avatar
Cylian Cylian is offline
Newcomer
 
Join Date: Oct 2008
Posts: 18
Default

Hi Adam,

I think that can easily been done with this following function:

Code:
Public Function Check4Match(inputString As String, pat As String) As Boolean '====================================================================== '/// Add a reference to Microsoft VBScript Regular Expressions 1.0, '/// goto Tools >> References from Excel Visual Basic Editor.... '/// function returns True if the matches are found.... '====================================================================== Dim RegExpObj As New RegExp With RegExpObj .IgnoreCase = True 'turn it off, if you need like to distinguish between 'A' and 'a' .Global = True .Pattern = pat Check4Match = .Test(inputString) End With End Function

To use this function you just call it like:

If Check4Match(<individual-cell-value>,<pattern-for-check>) = True then
...ColorIndex = 3
End If

<pattern-for-check> for three of your given options are:
  1. To match "-Q[number]-[number][number]" provide "-Q\d+-\d+" or most specifically "-Q\d-\d{1,2}"
  1. To match "-[3 LETTER MONTH ABBREVIATION][number]-[number][number]" provide "-((JAN)|(FEB)|(MAR)|(APR)|(MAY)|(JUN)|(JUL)|(AUG)|(SEP)|(OCT)|(NOV)|(DE C))\d+-\d+" or most specifically "-((JAN)|(FEB)|(MAR)|(APR)|(MAY)|(JUN)|(JUL)|(AUG)|(SEP)|(OCT)|(NOV)|(DE C))\d-\d{1,2}"
  1. To match "-[3 LETTER MONTH ABBREVIATION][number][number]-[number][number]" provide the same as above or most specifically "-((JAN)|(FEB)|(MAR)|(APR)|(MAY)|(JUN)|(JUL)|(AUG)|(SEP)|(OCT)|(NOV)|(DE C))\d{1,2}-\d{1,2}".

E.g.,

Code:
If Check4Match("454354-FEB2-09","-((JAN)|(FEB)|(MAR)|(APR)|(MAY)|(JUN)|(JUL)|(AUG)|(SEP)|(OCT)|(NOV)|(DEC))\d-\d{1,2}") = True then MsgBox "Pattern matched." End If

All queries are welcome. Happy coding...
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Excel / Possible Regex help
Excel / Possible Regex help
Excel / Possible Regex help Excel / Possible Regex help
Excel / Possible Regex help
Excel / Possible Regex help
Excel / Possible Regex help Excel / Possible Regex help Excel / Possible Regex help Excel / Possible Regex help Excel / Possible Regex help Excel / Possible Regex help Excel / Possible Regex help
Excel / Possible Regex help
Excel / Possible Regex help
 
Excel / Possible Regex help
Excel / Possible Regex help
 
-->