Look instr for specific string types

[Tom Hingston]

Hi

I need a function that will look in a cell and see (using instr) whether
certain expressions are there.
Examples of the expressions are:-
14 - 5 or 2 - 7 or 12 - 20
14/5 or 2/7 or 12/20
-320
155

I believe there are things called regular expressions but I am not sure how
to use them - or even if they will meet my needs because to values of the
numbers could be anything, just the formats of them are what I am looking
for. There may also be other irrelevant text in the cell.

Any help gratefully appreciated
Tom

[Tom Ogilvy]

If instr(cell.Value, "/") or instr(Cell.Value,"-") then

Also look at help on the Like operator; that may be closer to what you want.

That is about all I can garner from your explanation.

What is unique or special about 155?

Regards,
Tom Ogilvy

"Tom Hingston" <t...@NOSPAM.co.nz> wrote in message
news:e9$l6eb8BHA.2388@tkmsftngp05...

[Tom Hingston]

Thanks Tom

But I was looking for an expression like
** - **
**/**
-****
***

Where the * is a wild card number
I already knew about the basic instr function, I was looking for something
more powerful.

Tom

"Tom Ogilvy" <twog...@msn.com> wrote in message
news:uHcBuBd8BHA.1824@tkmsftngp05...

[Tom Ogilvy]

Did you look at the LIKE operator as I suggested.

If you were looking for something more powerful why have a subject like:

"Look instr for specific string types"

And then say:

> > I need a function that will look in a cell and see (using instr) whether
> > certain expressions are there.

No reason to keep your question a secret - go ahead and say what you want.

If you want to do regular expressions, here is some information previously
posted by Harlan Grove:

=====< extract from a posting by Harlan Grove >=====

I had given an implied warning that I might elaborate on the strictly
worksheet functions above. It's possible to use VBScript regular expressions
in Excel VBA, even under Excel 97. [VBScript 5 regular expressions can be
accessed directly by Excel 2000/VBA 6, but Excel 97/VBA 5 can't find them.
Please see Robert Bruce's reply to my posting in
microsoft.public.excel.programming under the subject 'How to Reference
VBScript Regular Expressions' for some details.]

Here's a url describing regular expressions:
http://msdn.microsoft.com/workshop/languages/clinic/scripting051099.asp

Here's a link to Microsoft's top scripting page:
http://msdn.microsoft.com/scripting/

If you have Internet Explorer 5 installed, you should have the VBScript 5
runtime.

Getting back to the point, using Robert Bruce's suggestion on using late
binding it's possible for Excel 97 to use VBScript regular expressions. They
can be wrapped inside VBA user-defined functions. Here's an example of a
rough equivalent to perl's s/// operator though with syntax closer to awk's
[g]sub().

Function psub(matchpat As String, replacepat As String, s As String,
Optional re_global As Boolean = True) As String
Dim regex As Object
Set regex = CreateObject("VBScript.RegExp")
regex.Pattern = matchpat
regex.Global = re_global
psub = regex.Replace(s, replacepat)
End Function

An example of how it could be used: =psub("[a-zA-Z]","",string)

Part #: ABC123-456X5B32 -> #: 123-456532
R102C1:R107C2 -> 1021:1072
WK 555-1212 -> 555-1212
234-5678PG -> 234-5678

It could be used as a more powerful find & replace. Here's an example of
changing just subcomponents of formulas matching '=', name or cell address,
'+', name or cell address, '/', name or cell address by making the first and
third name or cell address the arguments to myfunc() and otherfunc(),
respectively.

=psub("=(\w+)\+(\w+)/(\w+)","=myfunc($1)+$2/otherfunc($3)",C1)

=A1+A2/A11 -> =myfunc(A1)+A2/otherfunc(A11)
=A4/A5/A14 -> =A4/A5/A14
=A7+A8+A17 -> =A7+A8+A17
=A9+A10/A19=0 -> =myfunc(A9)+A10/otherfunc(A19)=0
=A10+A11/A20 -> =myfunc(A10)+A11/otherfunc(A20)

And here's another user-defined function that returns either the number of
pattern matches in a string (optional parameter = 0) or an array containing
the position and length of the n_th pattern match in a string (optional
parameter = n). This is roughly similar to awk's match().

Function pmatch(s As String, matchpat As String, Optional matchnum As Byte =
1) As Variant
Dim rval(1 To 3) As Variant, matchcollection As Object, regex As Object
Set regex = CreateObject("vbscript.RegExp")
regex.Pattern = matchpat
regex.Global = True
Set matchcollection = regex.Execute(s)
If matchnum = 0 Then
pmatch = matchcollection.Count
ElseIf matchnum <= matchcollection.Count Then
rval(1) = matchcollection.Item(matchnum).FirstIndex
rval(2) = matchcollection.Item(matchnum).Length
rval(3) = matchcollection.Item(matchnum).Value
pmatch = rval
Else
pmatch = CVErr(xlErrNum)
End If
End Function

=======< end of previous posting >=====

Regards,
Tom Ogilvy

Tom Hingston <t...@NOSPAM.co.nz> wrote in message

news:uXyud1g8BHA.2108@tkmsftngp02...

[Tom Hingston]

Tom,
Thanks for all that. It has given me something to work with. :-)

Note: I wasn't trying to keep anything a secret - I was just having trouble
describing what I wanted :-)

Tom

"Tom Ogilvy" <twog...@msn.com> wrote in message

news:u70$Rxk8BHA.1280@tkmsftngp07...