Searching for different strings?

[Steve Cohen]

I have a formula that currently searches for the string "risking" and enters
the amount found after it into the corosponding cell in colum A

=IF(ISERROR(SEARCH("risking",$G31)),"",VALUE(MID(G31,SEARCH("risking",$G31)+
8,SEARCH("to win",$G31)-SEARCH("risking",$G31)-8)))

I would like to ammend this to seach for "risking" or "withdrawal".

I can't seem for figure out the proper formatting I have tried
=IF(ISERROR(SEARCH("risking""withdrawal",$G31)),"",VALUE(MID(G31,SEARCH("ris
king""withdrawal",$G31)+8,SEARCH("to
win",$G31)-SEARCH("risking""withdrawal",$G31)-8)))

=IF(ISERROR(SEARCH("risking","withdrawal",$G31)),"",VALUE(MID(G31,SEARCH("ri
sking","withdrawal",$G31)+8,SEARCH("to
win",$G31)-SEARCH("risking","withdrawal",$G31)-8)))

and

=IF(ISERROR(SEARCH("risking""withdrawal",$G31)),"",VALUE(MID(G31,SEARCH("ris
king, withdrawal",$G31)+8,SEARCH("to win",$G31)-SEARCH("risking,
withdrawal",$G31)-8)))

The only difference is going to be that the amount win not be followed by
"to win" it will probably be followed by "Neteller", but not always.

 

[Dave Peterson]

I think that since withdrawl has 9 characters and risking has 8 and sometimes
the second value that you're looking for varies, I take a different approach.

I think I'd use a helper cell ($H31) and replace the first string and second
string with some unique characters.

I used this formula in $H31:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER($G31),"risking ","aaaaa"),
"withdrawl ","aaaaa")," to win","xxxxx")," neteller","xxxxx")
(all one cell)

I changed the case to lower, too. (If you're picking numbers out, then this
won't matter.) (=Substitute() is case sensitive.)

It replaces either "risking " or "withdrawl " with "aaaaa".
it replaces either " to win" or " neteller" with "xxxxx".

(Notice that I put an extra space in there, too! If you don't have those
spaces, then you'll have to fix it.)

Then I could look for "aaaaa" and "xxxxx" no matter what was in the original
cell:

=IF(AND(ISNUMBER(SEARCH("aaaaa",$H31)),ISNUMBER(SEARCH("xxxxx",$H31))),
MID($H31,SEARCH("aaaaa",$H31)+5,
SEARCH("xxxxx",$H31)-SEARCH("aaaaa",$H31)-5),"")
(all one cell)

And if you want to return a number:

=IF(AND(ISNUMBER(SEARCH("aaaaa",$H31)),ISNUMBER(SEARCH("xxxxx",$H31))),
--MID($H31,SEARCH("aaaaa",$H31)+5,
SEARCH("xxxxx",$H31)-SEARCH("aaaaa",$H31)-5),"")

(there's a double minus in the second line of the formula that will convert text
to numbers.)

 

[Steve Cohen]

Dave;

This is working some what. the helper cell H31 is returning the subsituted
text "aaaaa$400 toxxxxx", but A31 is giving me a "#VALUE!"

I tried changing the H31 formula
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER($G31),"risking
","aaaaa"),"withdrawal ","aaaaa")," to win","xxxxx")," neteller","xxxxx")
to
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER($G31),"risking","aaaaa"),
"withdrawal","aaaaa"),"to win","xxxxx"),"neteller","xxxxx")

Basically adding the space back into the return so it is now "aaaaa $400 to
xxxxx", but I still get a return of "#VALUE!" in A31.

=IF(AND(ISNUMBER(SEARCH("aaaaa",$H7)),ISNUMBER(SEARCH("xxxxx",$H7))),--MID($
H7,SEARCH("aaaaa",$H7)+5,SEARCH("xxxxx",$H7)-SEARCH("aaaaa",$H7)-5),"")

I am using the double minus, because a need a number returned so I can then
take that number and use it in other calculations.

There is an HTML version of it at the following message board if looking at
it will help.

http://www.mrexcel.com/board2/viewtopic.php?p=333714#333714

Thanks

Steve

 

[Steve Cohen]

The link is actually
http://www.mrexcel.com/board2/viewtopic.php?p=333703#333703

Dave;

This is working some what. the helper cell H31 is returning the subsituted
text "aaaaa$400 toxxxxx", but A31 is giving me a "#VALUE!"

I tried changing the H31 formula
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER($G31),"risking
","aaaaa"),"withdrawal ","aaaaa")," to win","xxxxx")," neteller","xxxxx")
to
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER($G31),"risking","aaaaa"),
"withdrawal","aaaaa"),"to win","xxxxx"),"neteller","xxxxx")

Basically adding the space back into the return so it is now "aaaaa $400 to
xxxxx", but I still get a return of "#VALUE!" in A31.

=IF(AND(ISNUMBER(SEARCH("aaaaa",$H7)),ISNUMBER(SEARCH("xxxxx",$H7))),--MID($
H7,SEARCH("aaaaa",$H7)+5,SEARCH("xxxxx",$H7)-SEARCH("aaaaa",$H7)-5),"")

I am using the double minus, because a need a number returned so I can then
take that number and use it in other calculations.

There is an HTML version of it at the following message board if looking at
it will help.

http://www.mrexcel.com/board2/viewtopic.php?p=333714#333714

Thanks

Steve

I think that since withdrawl has 9 characters and risking has 8 and sometimes
the second value that you're looking for varies, I take a different approach.

I think I'd use a helper cell ($H31) and replace the first string and second
string with some unique characters.

I used this formula in $H31:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER($G31),"risking ","aaaaa"),
"withdrawl ","aaaaa")," to win","xxxxx")," neteller","xxxxx")
(all one cell)

I changed the case to lower, too. (If you're picking numbers out, then this
won't matter.) (=Substitute() is case sensitive.)

It replaces either "risking " or "withdrawl " with "aaaaa".
it replaces either " to win" or " neteller" with "xxxxx".

(Notice that I put an extra space in there, too! If you don't have those
spaces, then you'll have to fix it.)

Then I could look for "aaaaa" and "xxxxx" no matter what was in the original
cell:

=IF(AND(ISNUMBER(SEARCH("aaaaa",$H31)),ISNUMBER(SEARCH("xxxxx",$H31))),
MID($H31,SEARCH("aaaaa",$H31)+5,
SEARCH("xxxxx",$H31)-SEARCH("aaaaa",$H31)-5),"")
(all one cell)

And if you want to return a number:

=IF(AND(ISNUMBER(SEARCH("aaaaa",$H31)),ISNUMBER(SEARCH("xxxxx",$H31))),
--MID($H31,SEARCH("aaaaa",$H31)+5,
SEARCH("xxxxx",$H31)-SEARCH("aaaaa",$H31)-5),"")

(there's a double minus in the second line of the formula that will convert text
to numbers.)

=IF(ISERROR(SEARCH("risking",$G31)),"",VALUE(MID(G31,SEARCH("risking",$G31)+=IF(ISERROR(SEARCH("risking""withdrawal",$G31)),"",VALUE(MID(G31,SEARCH("ris=IF(ISERROR(SEARCH("risking","withdrawal",$G31)),"",VALUE(MID(G31,SEARCH("ri=IF(ISERROR(SEARCH("risking""withdrawal",$G31)),"",VALUE(MID(G31,SEARCH("ris

 

[Dave Peterson]

Since you're getting back "toxxxxx", maybe you have "to neteller" instead of
just "neteller".

Make sure that substitute() formula includes all the characters you want to get
rid of. (I didn't look at the HTML version.)

And until you have all the stuff correct, get rid of the "--" bit. Then you'll
see what's returned. When you get all the correct digits (and just the correct
digits), put the "--" back.

 

[Steve Cohen]

Dave

I got it working, I'm not sure what was going on the first time I did the
same thing.
The only thing I'm running into now is that I've added some additional
substitutions, but Excel seems to only handle 7 arguements and I need to fit
at least 1 or 2 more.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUT
E(SUBSTITUTE(LOWER $G6),"risking","aaaaa"),"cash
back","aaaaa"),"payout","aaaaa"),"reup","aaaaa"),"deposit","aaaaa"),"bonus",
"aaaaa"),"to","xxxxx"),"from","xxxxx")

Do you know any way around this.
I had a similar thing with an if statement and some suggested an If Or
statement.

=IF(F6="Win",ABS(B6),IF(OR(F6="Deposit",F6="Bonus",F6="Transfer
From",F6="Cash
Back"),ABS(A6),IF(OR(F6="Withdrawal",F6="Bet",F6="Loss",F6="Transfer
To",F6="Fee"),-ABS(A6),IF(OR(F6="Void",F6="Push"),+ABS(A6) -ABS(A6),""))))

Thanks;

Steve

 

[Dave Peterson]

Sooner or later, I'd go to a user defined function. This is code that is
sometimes easier to understand/update.

You can use it just like a builtin function like =mid(), =left(), etc.

But you have to do a little work first.

If you make a mistake, remember not to save your workbook--or make a backup
first.


Option Explicit
Function ExtractNumbers(myStr As String) As Variant

Dim myStarts As Variant
Dim myEnds As Variant
Dim StartPos As Long
Dim EndPos As Long
Dim iCtr As Long

myStr = LCase(myStr)

myStarts = Array("risking", "cash back", "payout", "reup", "deposit")
myEnds = Array("to", "from")

For iCtr = LBound(myStarts) To UBound(myStarts)
If InStr(1, myStr, LCase(myStarts(iCtr))) > 0 Then
myStr = Application.Substitute(myStr, LCase(myStarts(iCtr)), Chr(1))
Exit For
End If
Next iCtr

For iCtr = LBound(myEnds) To UBound(myEnds)
If InStr(1, myStr, LCase(myEnds(iCtr))) > 0 Then
myStr = Application.Substitute(myStr, myEnds(iCtr), Chr(2))
Exit For
End If
Next iCtr

StartPos = InStr(1, myStr, Chr(1)) + 1
EndPos = InStr(1, myStr, Chr(2))

If StartPos > 0 _
And EndPos > 0 Then
ExtractNumbers = Trim(Mid(myStr, StartPos, EndPos - StartPos))
Else
ExtractNumbers = CVErr(xlErrRef)
End If

End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

=======

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Type
=extractnumbers(a1)
in a cell (but point at the cell with the string).

Just keep adding more words/phrases to the myStarts array and the myEnds array.

Now you can dump that intermediate helper cell and just use that formula
directly.

Dave

I got it working, I'm not sure what was going on the first time I did the
same thing.
The only thing I'm running into now is that I've added some additional
substitutions, but Excel seems to only handle 7 arguements and I need to fit
at least 1 or 2 more.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUT
E(SUBSTITUTE(LOWER $G6),"risking","aaaaa"),"cash
back","aaaaa"),"payout","aaaaa"),"reup","aaaaa"),"deposit","aaaaa"),"bonus",
"aaaaa"),"to","xxxxx"),"from","xxxxx")

Do you know any way around this.
I had a similar thing with an if statement and some suggested an If Or
statement.

=IF(F6="Win",ABS(B6),IF(OR(F6="Deposit",F6="Bonus",F6="Transfer
From",F6="Cash
Back"),ABS(A6),IF(OR(F6="Withdrawal",F6="Bet",F6="Loss",F6="Transfer
To",F6="Fee"),-ABS(A6),IF(OR(F6="Void",F6="Push"),+ABS(A6) -ABS(A6),""))))

Thanks;

Steve

 

[Steve Cohen]

Dave;

That was perfect.

VB is still way over my head.

Thanks for the help.

Steve

 

[Steve Cohen]

Actually, It's almost perfect.
There is one small problem, and it's something that I ran into when I first
for the formula to find just Risking and take the amount after it.

The information that is being populated in Colum A now needs to be a value.
It is not being recognized by some of my functions that look to it from
other worksheets.

Thanks;

Steve

Dave;

That was perfect.

VB is still way over my head.

Thanks for the help.

Steve

Sooner or later, I'd go to a user defined function. This is code that is
sometimes easier to understand/update.

You can use it just like a builtin function like =mid(), =left(), etc.

But you have to do a little work first.

If you make a mistake, remember not to save your workbook--or make a backup
first.


Option Explicit
Function ExtractNumbers(myStr As String) As Variant

Dim myStarts As Variant
Dim myEnds As Variant
Dim StartPos As Long
Dim EndPos As Long
Dim iCtr As Long

myStr = LCase(myStr)

myStarts = Array("risking", "cash back", "payout", "reup", "deposit")
myEnds = Array("to", "from")

For iCtr = LBound(myStarts) To UBound(myStarts)
If InStr(1, myStr, LCase(myStarts(iCtr))) > 0 Then
myStr = Application.Substitute(myStr, LCase(myStarts(iCtr)), Chr(1))
Exit For
End If
Next iCtr

For iCtr = LBound(myEnds) To UBound(myEnds)
If InStr(1, myStr, LCase(myEnds(iCtr))) > 0 Then
myStr = Application.Substitute(myStr, myEnds(iCtr), Chr(2))
Exit For
End If
Next iCtr

StartPos = InStr(1, myStr, Chr(1)) + 1
EndPos = InStr(1, myStr, Chr(2))

If StartPos > 0 _
And EndPos > 0 Then
ExtractNumbers = Trim(Mid(myStr, StartPos, EndPos - StartPos))
Else
ExtractNumbers = CVErr(xlErrRef)
End If

End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

=======

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Type
=extractnumbers(a1)
in a cell (but point at the cell with the string).

Just keep adding more words/phrases to the myStarts array and the myEnds array.

Now you can dump that intermediate helper cell and just use that formula
directly.

to

fit

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTback","aaaaa"),"payout","aaaaa"),"reup","aaaaa"),"deposit","aaaaa"),"bonus",=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER($G31),"risking","aaaaa"), "aaaaa

$400

=IF(AND(ISNUMBER(SEARCH("aaaaa",$H7)),ISNUMBER(SEARCH("xxxxx",$H7))),--MID($ string

and

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER($G31),"risking=IF(ISERROR(SEARCH("risking",$G31)),"",VALUE(MID(G31,SEARCH("risking",$G31)+=IF(ISERROR(SEARCH("risking""withdrawal",$G31)),"",VALUE(MID(G31,SEARCH("ris=IF(ISERROR(SEARCH("risking","withdrawal",$G31)),"",VALUE(MID(G31,SEARCH("ri=IF(ISERROR(SEARCH("risking""withdrawal",$G31)),"",VALUE(MID(G31,SEARCH("ris

 

[Dave Peterson]

What do you have in that cell that isn't returning a value?

Are you saying you have something like:

erqwer asdf qw risking 1234
so nothing else follows?

And maybe you'd have:
1234 to neteller

if that's true, we can just cheat and fudge in a starting and ending point.

Option Explicit
Function ExtractNumbers(myStr As String) As Variant

Dim myStarts As Variant
Dim myEnds As Variant
Dim StartPos As Long
Dim EndPos As Long
Dim iCtr As Long

myStr = LCase(myStr)

myStarts = Array("risking", "cash back", "payout", "reup", "deposit")
myEnds = Array("to", "from")

For iCtr = LBound(myStarts) To UBound(myStarts)
If InStr(1, myStr, LCase(myStarts(iCtr))) > 0 Then
myStr = Application.Substitute(myStr, LCase(myStarts(iCtr)), Chr(1))
Exit For
End If
Next iCtr

For iCtr = LBound(myEnds) To UBound(myEnds)
If InStr(1, myStr, LCase(myEnds(iCtr))) > 0 Then
myStr = Application.Substitute(myStr, myEnds(iCtr), Chr(2))
Exit For
End If
Next iCtr

StartPos = InStr(1, myStr, Chr(1)) + 1
EndPos = InStr(1, myStr, Chr(2))

If EndPos = 0 Then
EndPos = Len(myStr) + 1
End If

ExtractNumbers = Trim(Mid(myStr, StartPos, EndPos - StartPos))

End Function

There was a slight problem in the previous code anyway:

This line:
If StartPos > 0 _
And EndPos > 0 Then
had a problem.

I always added 1 to StartPos. So that portion of the check never did anything
anyway!

Actually, It's almost perfect.
There is one small problem, and it's something that I ran into when I first
for the formula to find just Risking and take the amount after it.

The information that is being populated in Colum A now needs to be a value.
It is not being recognized by some of my functions that look to it from
other worksheets.

Thanks;

Steve

 

[Steve Cohen]

No I have a cells in colum G on worksheet Trans_type that looks for the word
BET in Colum F on my various other worksheet and then if it sees it looks
over to Colum A to see the amount that has been bet.
This shows me how much money I have bet on that particular book and then on
all my books combined.
The formula I have in colum G is =SUM(SUMIF('Bet CBS'!F:F,{"*BET*"},'Bet
CBS'!A:A))

This has gotten pretty vomplicated, but through the help of people like your
self I'm getting it the way I want it.

Thanks

Steve.

 

[Steve Cohen]

I put VALUE in front of =extracnumber(g6) so =VALUE(extractnumbers(G6))
works to provide a value that my other functions can see.

Thanks for the help

Steve

No I have a cells in colum G on worksheet Trans_type that looks for the word
BET in Colum F on my various other worksheet and then if it sees it looks
over to Colum A to see the amount that has been bet.
This shows me how much money I have bet on that particular book and then on
all my books combined.
The formula I have in colum G is =SUM(SUMIF('Bet CBS'!F:F,{"*BET*"},'Bet
CBS'!A:A))

This has gotten pretty vomplicated, but through the help of people like your
self I'm getting it the way I want it.

Thanks

Steve.

What do you have in that cell that isn't returning a value?

Are you saying you have something like:

erqwer asdf qw risking 1234
so nothing else follows?

And maybe you'd have:
1234 to neteller

if that's true, we can just cheat and fudge in a starting and ending point.

Option Explicit
Function ExtractNumbers(myStr As String) As Variant

Dim myStarts As Variant
Dim myEnds As Variant
Dim StartPos As Long
Dim EndPos As Long
Dim iCtr As Long

myStr = LCase(myStr)

myStarts = Array("risking", "cash back", "payout", "reup", "deposit")
myEnds = Array("to", "from")

For iCtr = LBound(myStarts) To UBound(myStarts)
If InStr(1, myStr, LCase(myStarts(iCtr))) > 0 Then
myStr = Application.Substitute(myStr, LCase(myStarts(iCtr)), Chr(1))
Exit For
End If
Next iCtr

For iCtr = LBound(myEnds) To UBound(myEnds)
If InStr(1, myStr, LCase(myEnds(iCtr))) > 0 Then
myStr = Application.Substitute(myStr, myEnds(iCtr), Chr(2))
Exit For
End If
Next iCtr

StartPos = InStr(1, myStr, Chr(1)) + 1
EndPos = InStr(1, myStr, Chr(2))

If EndPos = 0 Then
EndPos = Len(myStr) + 1
End If

ExtractNumbers = Trim(Mid(myStr, StartPos, EndPos - StartPos))

End Function

There was a slight problem in the previous code anyway:

This line:
If StartPos > 0 _
And EndPos > 0 Then
had a problem.

I always added 1 to StartPos. So that portion of the check never did anything
anyway!

I

did

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTback","aaaaa"),"payout","aaaaa"),"reup","aaaaa"),"deposit","aaaaa"),"bonus", characters

you

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER($G31),"risking","aaaaa"),=IF(AND(ISNUMBER(SEARCH("aaaaa",$H7)),ISNUMBER(SEARCH("xxxxx",$H7))),--MID($ returned
so I board

if

was

in

=IF(ISERROR(SEARCH("risking",$G31)),"",VALUE(MID(G31,SEARCH("risking",$G31)+=IF(ISERROR(SEARCH("risking""withdrawal",$G31)),"",VALUE(MID(G31,SEARCH("ris=IF(ISERROR(SEARCH("risking","withdrawal",$G31)),"",VALUE(MID(G31,SEARCH("ri=IF(ISERROR(SEARCH("risking""withdrawal",$G31)),"",VALUE(MID(G31,SEARCH("ris not

be

but