Matching strings with a pattern

[Raj]

Hi,

I have cells containing strings of the type "Mumbai - 400078" and
"Belgaum - 590011" with the following pattern:
Alphabetic string followed by space followed by hyphen followed by
space and then a numeric string. I want to fill such cells with a
color (interior.colorindex)? How do I identify such cells to the code?

Thanks in Advance for the help.

Regards,
Raj

 

[Gary Keramidas]

didn't give much information but maybe some kind of loop with

With Range("A1")
If InStr(1, .Value, " - ") > 1 Then
..Interior.ColorIndex = 19
End If
End With

 

[Ron Rosenfeld]

Hi,

I have cells containing strings of the type "Mumbai - 400078" and
"Belgaum - 590011" with the following pattern:
Alphabetic string followed by space followed by hyphen followed by
space and then a numeric string. I want to fill such cells with a
color (interior.colorindex)? How do I identify such cells to the code?

Thanks in Advance for the help.

Regards,
Raj

Here's an example of one way of doing this using a regular expression to match
your pattern:

==========================================
Option Explicit
Sub cSpec()
Dim rg As Range, c As Range
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "^[A-Z]+\s-\s\d+$"
re.Global = False
re.ignorecase = True
Set rg = Range("A1:A10")
For Each c In rg
If re.test(c) = True Then
c.Interior.ColorIndex = 3
Else
c.Interior.ColorIndex = xlColorIndexNone
End If
Next c
End Sub
=========================================
--ron

 

[Rick Rothstein]

If your number string is **always** six digits long, you can do this...

Sub ColorCells()
Dim Cell As Range, YourRange As Range
Set YourRange = Worksheets("Sheet2").Range("A1:C100")
YourRange.Interior.ColorIndex = xlColorIndexNone
For Each Cell In YourRange
If Cell Like "* - ######" Then Cell.Interior.ColorIndex = 3
Next
End Sub

 

[Raj]

Thanks a ton, everyone. I tried Rick's solution and it worked. I will
try out and study Ron's Regex solution next.

Regards,
Raj

 

[Ron Rosenfeld]

If your number string is **always** six digits long, you can do this...

Sub ColorCells()
Dim Cell As Range, YourRange As Range
Set YourRange = Worksheets("Sheet2").Range("A1:C100")
YourRange.Interior.ColorIndex = xlColorIndexNone
For Each Cell In YourRange
If Cell Like "* - ######" Then Cell.Interior.ColorIndex = 3
Next
End Sub

I interpreted the OP to mean by "alphabetic" that that string only contained
letters. Yours will match any character, or even no character, in the first
part of the string.

As a matter of fact, the "*" is superfluous in your pattern.
--ron

 

[Ron Rosenfeld]

I interpreted the OP to mean by "alphabetic" that that string only contained
letters.

I should have written "letters in the first part"

--ron

 

[Rick Rothstein]

Ron has raised a good point about your original specification and what my
code does to meet it. You said that the part before the space/dash/space
should be alphabetic... my code allows that part of the text being tested to
be any characters, not just letters of the alphabet. The function below will
test the text to see if the first part is, in fact, alphabetic while the
second part is six digits (with both parts delimited by the space/dash/space
characters). So, use this instead of the code I posted earlier...

Sub ColorCells()
Dim Cell As Range, YourRange As Range
Set YourRange = Worksheets("Sheet2").Range("A1:C100")
YourRange.Interior.ColorIndex = xlColorIndexNone
For Each Cell In YourRange
If Not Cell Like "*[!A-Za-z]* - ######" And Cell Like _
"* - ######" Then Cell.Interior.ColorIndex = 3
Next
End Sub

 

[Rick Rothstein]

If your number string is **always** six digits long, you can do this...

I interpreted the OP to mean by "alphabetic" that that string only
contained
letters. Yours will match any character, or even no character, in the
first
part of the string.

As a matter of fact, the "*" is superfluous in your pattern.

Good point Ron! I completely overlooked that. I just posted some corrected
code for the OP (still using the Like operator test method).

Going back to my original code though... what did you mean the "*" is
superfluous in your pattern? If we leave it out, then this test will fail...

If "Mumbai - 400078" like " - ######" Then

Or did I miss the point of your comment?

 

[Ron Rosenfeld]

Good point Ron! I completely overlooked that. I just posted some corrected
code for the OP (still using the Like operator test method).

Going back to my original code though... what did you mean the "*" is
superfluous in your pattern? If we leave it out, then this test will fail...

If "Mumbai - 400078" like " - ######" Then

Or did I miss the point of your comment?

I was not clear (in either my writing or my own mind

I was trying to express that your original code, as well as your current code,
will match a string with zero characters prior to the <space><hyphen><space>

In other words, a string: ' - 123456' will match (in both of your code
examples).

Also, and I don't know how this may apply to the OP's problem, your code allows
a first string of any length, including zero, and the second string must be
exactly six digits (which you did specify in your initial post).

In mine, both strings must be at least 1 character, with no upper limit. But
it would be trivial to add more restrictive length parameters. For example, if
six digits were required at the end, then:

re.Pattern = "^[A-Z]+\s-\s\d+$"

gets changed to

re.Pattern = "^[A-Z]+\s-\s\d{6}$"

--ron

 

[Rick Rothstein]

One more change in response to a comment by Ron. It seems that my code will
allow no characters to appear before the space/dash/space and that is should
require at least one character (or more) to be in that position. Ron raises
a good point, so here is the adjusted code to cater to this requirement as
well...

Sub ColorCells()
Dim Cell As Range, YourRange As Range
Set YourRange = Worksheets("Sheet2").Range("A1:C100")
YourRange.Interior.ColorIndex = xlColorIndexNone
For Each Cell In YourRange
If Not Cell Like "*[!A-Za-z]* - ######" And Cell Like "* - ######" _
And Cell Like "?* - *" Then Cell.Interior.ColorIndex = 3
Next
End Sub

--
Rick (MVP - Excel)

Ron has raised a good point about your original specification and what my
code does to meet it. You said that the part before the space/dash/space
should be alphabetic... my code allows that part of the text being tested
to be any characters, not just letters of the alphabet. The function below
will test the text to see if the first part is, in fact, alphabetic while
the second part is six digits (with both parts delimited by the
space/dash/space characters). So, use this instead of the code I posted
earlier...

Sub ColorCells()
Dim Cell As Range, YourRange As Range
Set YourRange = Worksheets("Sheet2").Range("A1:C100")
YourRange.Interior.ColorIndex = xlColorIndexNone
For Each Cell In YourRange
If Not Cell Like "*[!A-Za-z]* - ######" And Cell Like _
"* - ######" Then Cell.Interior.ColorIndex = 3
Next
End Sub

--
Rick (MVP - Excel)

Thanks a ton, everyone. I tried Rick's solution and it worked. I will
try out and study Ron's Regex solution next.

Regards,
Raj

 

[Rick Rothstein]

Good point Ron! I completely overlooked that. I just posted some corrected

I was not clear (in either my writing or my own mind

I was trying to express that your original code, as well as your current
code,
will match a string with zero characters prior to the
<space><hyphen><space>

In other words, a string: ' - 123456' will match (in both of your code
examples).

Also, and I don't know how this may apply to the OP's problem, your code
allows
a first string of any length, including zero, and the second string must
be
exactly six digits (which you did specify in your initial post).

That's a reasonable observation... thanks for noting it. I just posted
revised code to make sure at least one character exists before the
space/dash/space.

 

[Ron Rosenfeld]

That's a reasonable observation... thanks for noting it. I just posted
revised code to make sure at least one character exists before the
space/dash/space.

I noted your last revision. Of course, these kinds of modifications seem
simpler to me with regular expressions, than with sequential Like statements.
But that's just a matter of taste.
--ron