Hi
Should be easy but I can't work out the expression to raise an exception to
the string or sub string FAO
This doesn't work [a-zA-Z\-\.]\s?|(?!)FAO

A cell might acceptably contain
Name or
Name-Name or
Name - Name or
.. (dot)

but in error may contain
Name FAO or
Name-Name FAO or
Name - Name FAO or just
FAO

How can I prevent FAO from making a match?

Geoff

Does it have to be a RegEx? What about a test something like this...

If Not TextValue Like "*[!a-zA-Z.-]*" And Right(TextValue, 3) < "FAO" Then
MsgBox "Looks like a valid name to me"
Else
MsgBox "That name is not valid"
End If

--
Rick (MVP - Excel)


"Geoff K" wrote in message
...
Hi
Should be easy but I can't work out the expression to raise an exception
to
the string or sub string FAO
This doesn't work [a-zA-Z\-\.]\s?|(?!)FAO

A cell might acceptably contain
Name or
Name-Name or
Name - Name or
. (dot)

but in error may contain
Name FAO or
Name-Name FAO or
Name - Name FAO or just
FAO

How can I prevent FAO from making a match?

Geoff

Sorted....
Because I'm looking at first and last names I now look to match digits, / or
FAO with
"[/0-9]|F\s*A\s*O\s*" making sure that .IgnoreCase= True

It's worked so far.

Geoff

"Geoff K" wrote:

Hi
Should be easy but I can't work out the expression to raise an exception to
the string or sub string FAO
This doesn't work [a-zA-Z\-\.]\s?|(?!)FAO

A cell might acceptably contain
Name or
Name-Name or
Name - Name or
. (dot)

but in error may contain
Name FAO or
Name-Name FAO or
Name - Name FAO or just
FAO

How can I prevent FAO from making a match?

Geoff

On Thu, 15 Oct 2009 08:56:46 -0700, Geoff K
wrote:

Hi
Should be easy but I can't work out the expression to raise an exception to
the string or sub string FAO
This doesn't work [a-zA-Z\-\.]\s?|(?!)FAO

A cell might acceptably contain
Name or
Name-Name or
Name - Name or
. (dot)

but in error may contain
Name FAO or
Name-Name FAO or
Name - Name FAO or just
FAO

How can I prevent FAO from making a match?

Geoff

If you want to exclude any line that contains FAO, you could use the Replace
method with this regex:

".*FAO.*"

It will return a null string for any string that contains FAO.

Or you could use the Test method -- but the result would be False.
--ron

On Fri, 16 Oct 2009 20:26:04 -0400, Ron Rosenfeld
wrote:

If you want to exclude any line that contains FAO, you could use the Replace
method with this regex:

".*FAO.*"

It will return a null string for any string that contains FAO.

Or you could use the Test method -- but the result would be False.

Just to elaborate on the use of the Test method. If the string is to be
accepted (i.e. does NOT contain FAO), then the result of the Test method will
be false.
--ron