Hello,

The snippet of code below builds a new string "Newval" based on the pattern
[A-Z]
and that works fine but what if I wanted to exclude values .
For example I could set a pattern of "[)(*&^]" how would I code it to
exclude those values?

With RegExp
.Global = True
.Pattern = "[A-Z]"
End With
Set Myrange = ActiveSheet.Range("A1:A10")
For Each C In Myrange
newval = ""
Set Collection = RegExp.Execute(C.Value)
For Each RegMatch In Collection
newval = newval & RegMatch
Next
Next


D

On Wed, 19 Sep 2007 12:24:02 -0700, Dave
wrote:

For example I could set a pattern of "[)(*&^]" how would I code it to
exclude those values?

.Pattern = "[^)(*&\^]"

Note the \ that precedes the second ^. The first ^ defines the character
sequence as negative and the \ escapes the second ^ so it's included in the
negative character sequence.
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

On Wed, 19 Sep 2007 12:24:02 -0700, Dave
wrote:

Hello,

The snippet of code below builds a new string "Newval" based on the pattern
[A-Z]
and that works fine but what if I wanted to exclude values .
For example I could set a pattern of "[)(*&^]" how would I code it to
exclude those values?

With RegExp
.Global = True
.Pattern = "[A-Z]"
End With
Set Myrange = ActiveSheet.Range("A1:A10")
For Each C In Myrange
newval = ""
Set Collection = RegExp.Execute(C.Value)
For Each RegMatch In Collection
newval = newval & RegMatch
Next
Next


D

Perhaps you just need to negate the character class:

"[^)(*&^]"

which translates as:

Match a single character NOT present in the list
")(*&^"


--ron

On Wed, 19 Sep 2007 17:59:04 -0500, Dick Kusleika wrote:

On Wed, 19 Sep 2007 12:24:02 -0700, Dave
wrote:

For example I could set a pattern of "[)(*&^]" how would I code it to
exclude those values?

.Pattern = "[^)(*&\^]"

Note the \ that precedes the second ^. The first ^ defines the character
sequence as negative and the \ escapes the second ^ so it's included in the
negative character sequence.

I don't believe there is any need to escape the ^ in VBScript. Both by
testing, and by documentation, it seems to be unnecessary.

http://msdn2.microsoft.com/en-us/library/ta6y6h4z.aspx

"If the caret character appears in any other position within the list, it
matches itself, that is, it has no special meaning. "


--ron

On Wed, 19 Sep 2007 20:14:28 -0400, Ron Rosenfeld
wrote:

On Wed, 19 Sep 2007 17:59:04 -0500, Dick Kusleika wrote:

On Wed, 19 Sep 2007 12:24:02 -0700, Dave
wrote:

For example I could set a pattern of "[)(*&^]" how would I code it to
exclude those values?

.Pattern = "[^)(*&\^]"

Note the \ that precedes the second ^. The first ^ defines the character
sequence as negative and the \ escapes the second ^ so it's included in the
negative character sequence.

I don't believe there is any need to escape the ^ in VBScript. Both by
testing, and by documentation, it seems to be unnecessary.

http://msdn2.microsoft.com/en-us/library/ta6y6h4z.aspx

"If the caret character appears in any other position within the list, it
matches itself, that is, it has no special meaning. "


--ron

Of course, that only applies when the ^ appears within brackets as other than
the first character.
--ron

On Wed, 19 Sep 2007 20:14:28 -0400, Ron Rosenfeld
wrote:

On Wed, 19 Sep 2007 17:59:04 -0500, Dick Kusleika wrote:

On Wed, 19 Sep 2007 12:24:02 -0700, Dave
wrote:

For example I could set a pattern of "[)(*&^]" how would I code it to
exclude those values?

.Pattern = "[^)(*&\^]"

Note the \ that precedes the second ^. The first ^ defines the character
sequence as negative and the \ escapes the second ^ so it's included in the
negative character sequence.

I don't believe there is any need to escape the ^ in VBScript. Both by
testing, and by documentation, it seems to be unnecessary.

http://msdn2.microsoft.com/en-us/library/ta6y6h4z.aspx

"If the caret character appears in any other position within the list, it
matches itself, that is, it has no special meaning. "


Ron: You're right. Mine was actually excluding the slash too. Thanks for
the correction.
--
Dick