Is there a character in excel that represents "like"? I need to use the following formula:

=SUBSTITUTE(A3," ($XX.XX)","")

Where the old text may be any dollar amount (1.93, 5678.35, 10000.00 ...)

TIA
-Chris

Chris,

You need to use a custom User-Defined-Function, code below. Copy it into a
module in your workbook, then use it like:

=mySub(A3," ($??.??)","")

HTH,
Bernie
MS Excel MVP

Function MySub(inCell As String, _
myLike As String, _
myRep As String) As String
Dim i As Integer
Dim iP As Integer
Dim myStr As String

iP = 0
If inCell Like "*" & myLike & "*" Then
For i = 1 To Len(inCell) - _
Len(Replace(inCell, Left(myLike, 1), ""))
iP = InStr(iP + 1, inCell, Left(myLike, 1))
myStr = Mid(inCell, iP, Len(myLike))
If myStr Like myLike Then
MySub = Left(inCell, iP - 1) & myRep & _
Mid(inCell, iP + Len(myLike), Len(inCell))
Exit Function
End If
Next i
End If
MySub = inCell
End Function


"Chris" wrote in message
...
Is there a character in excel that represents "like"? I need to use the
following formula:

=SUBSTITUTE(A3," ($XX.XX)","")

Where the old text may be any dollar amount (1.93, 5678.35, 10000.00 ...)

TIA
-Chris

Hi Bernie, RegExpr (Regular Expressions)
Your UDF works as advertised for what it was asked to do
but I'm having a hard time understanding it -- lack of understanding
might even be okay if I at least knew how to use it.

is the space in the second parameter MyLike relevant
No matter what I put into the third parameter, MyRep, it doesn't
seem to make any difference -- what would make a difference.

I suspect that this is a very general purpose subroutine but I can't
make it do anything else. For example would it be able to
extract the phone number from
Phone 555-123-4578 ext 145
Tried the following all it did was return the original string.
=mySub(A13,"###-###-####","")

Also how do the parens in the original actually function
=mySub(A3," ($??.??)","")
Tried it without the parens and got the same returned values.

I looked up parentheses and found
(pattern) Matches pattern and captures the match.
(?:pattern) Matches pattern but does not capture the match
(?=pattern) Positive lookaheads
(?!pattern) Negative lookaheads
to I tried the following still just returned original string:
=mySub(A13,"###-###-####","")

Reference:
http://msdn.microsoft.com/library/de...beginnings.asp

TIA, using Excel 2000
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Bernie Deitrick" <deitbe @ consumer dot org wrote...
[clipped,... see either Google in twelve hours, or from Microsoft server:
http://google.com/groups?threadm=ebX...GP10.phx.g bl



"Chris" wrote ...
Is there a character in excel that represents "like"? I need to use the
following formula:

=SUBSTITUTE(A3," ($XX.XX)","")

Where the old text may be any dollar amount (1.93, 5678.35, 10000.00 ...)

Chris,

I missed your last part: you'll need to use my function like this:

=mysub(A3," ($" & REPT("?",FIND(".",A3)-FIND("$",A3)-1) &".??)","")

to account for the variation in magnitude of the currency amount.

Sorry about that.

Bernie
MS Excel MVP

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Chris,

You need to use a custom User-Defined-Function, code below. Copy it into
a
module in your workbook, then use it like:

=mySub(A3," ($??.??)","")

HTH,
Bernie
MS Excel MVP

Function MySub(inCell As String, _
myLike As String, _
myRep As String) As String
Dim i As Integer
Dim iP As Integer
Dim myStr As String

iP = 0
If inCell Like "*" & myLike & "*" Then
For i = 1 To Len(inCell) - _
Len(Replace(inCell, Left(myLike, 1), ""))
iP = InStr(iP + 1, inCell, Left(myLike, 1))
myStr = Mid(inCell, iP, Len(myLike))
If myStr Like myLike Then
MySub = Left(inCell, iP - 1) & myRep & _
Mid(inCell, iP + Len(myLike), Len(inCell))
Exit Function
End If
Next i
End If
MySub = inCell
End Function


"Chris" wrote in message
...
Is there a character in excel that represents "like"? I need to use the
following formula:

=SUBSTITUTE(A3," ($XX.XX)","")

Where the old text may be any dollar amount (1.93, 5678.35, 10000.00
....)

TIA
-Chris

David,

I'm not using regular expressions, just creating a function that uses VBA's
LIKE operator. My eyes kinda glaze over when "regular expressions" gets
mentioned, since I have no experience with them.

The space was important in the second expression simply because the OP used
it in his SUBSTITUTE function, presumably because he wanted to change

"This Text ($XX.XX)"

to

"This Text"

without the trailing space. That is the same reason for the parens: I
assumed he meant to look for a string like ($12.34) and remove it entirely.

I could have done this by using worksheet functions that looked for the
leading paren-dollar sign combo, and the next closing parens, but I wanted
to try using the wildcard approach.

I think that where we are diverging is in the desired result: if you have
this in cell A1:

Phone 555-123-4578 ext 145

then mySub used like this (note the leading space - the function doesn't
work if the second argument's leading character isn't actually part of the
string - though it could be rewritten to account for that):

=mysub(A1, " ???-???-????","")

would return the string

Phone ext 145

The function below my signature, myFind, used like

=myFind(A1, " ???-???-????","")

would return the nicely tiimmed string

555-123-4578

Also, I'm getting the feeling that the LIKE operator doesn't like #'s but
only likes *'s and ?'s.

I hope I haven't further confused the issue. You'll have to request a
tutorial (which I could use also....) on regular expressions and their use
in VBA from one of the smart MVPs ;-)

HTH,
Bernie
MS Excel MVP

Function MyFind(inCell As String, _
myLike As String, _
myRep As String) As String
Dim i As Integer
Dim iP As Integer
Dim myStr As String

iP = 0
If inCell Like "*" & myLike & "*" Then
For i = 1 To Len(inCell) - _
Len(Replace(inCell, Left(myLike, 1), ""))
iP = InStr(iP + 1, inCell, Left(myLike, 1))
myStr = Mid(inCell, iP, Len(myLike))
If myStr Like myLike Then
MyFind = Trim(myStr)
Exit Function
End If
Next i
End If
MyFind = inCell
End Function








"David McRitchie" wrote in message
...
Hi Bernie, RegExpr (Regular Expressions)
Your UDF works as advertised for what it was asked to do
but I'm having a hard time understanding it -- lack of understanding
might even be okay if I at least knew how to use it.

is the space in the second parameter MyLike relevant
No matter what I put into the third parameter, MyRep, it doesn't
seem to make any difference -- what would make a difference.

I suspect that this is a very general purpose subroutine but I can't
make it do anything else. For example would it be able to
extract the phone number from
Phone 555-123-4578 ext 145
Tried the following all it did was return the original string.
=mySub(A13,"###-###-####","")

Also how do the parens in the original actually function
=mySub(A3," ($??.??)","")
Tried it without the parens and got the same returned values.

I looked up parentheses and found
(pattern) Matches pattern and captures the match.
(?:pattern) Matches pattern but does not capture the match
(?=pattern) Positive lookaheads
(?!pattern) Negative lookaheads
to I tried the following still just returned original string:
=mySub(A13,"###-###-####","")

Reference:

http://msdn.microsoft.com/library/de...beginnings.asp

TIA, using Excel 2000
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Bernie Deitrick" <deitbe @ consumer dot org wrote...
[clipped,... see either Google in twelve hours, or from Microsoft server:
http://google.com/groups?threadm=ebX...GP10.phx.g bl



"Chris" wrote ...
Is there a character in excel that represents "like"? I need to use
the
following formula:

=SUBSTITUTE(A3," ($XX.XX)","")

Where the old text may be any dollar amount (1.93, 5678.35, 10000.00
....)

"Bernie Deitrick" <deitbe @ consumer dot org wrote in Also, I'm getting
the feeling that the LIKE operator doesn't like #'s but
only likes *'s and ?'s.

Actually, it does like #'s, just not my bone-headed use of them. If you
have

This BBB-CCC-DDDD 555-123-4578 ext 146

then

=myFind(A1, " ???-???-????","")

returns "BBB-CCC-DDDD" while

=myFind(A1, " ###-###-####","")

returns

555-123-4578

Bernie