if cell contains.....

  • Thread starter Thread starter Wally Steadman
  • Start date Start date
W

Wally Steadman

Hello all,
I have a text string in cell A1 and I am trying to do an If Statement that
would place a certain value in B1 if something is contained in the text
string. I know this is probably simple, but have looked and I am stumped.

Cell A1 contains the following:
"a bulk order deed blessed small bulk order normal leather normal amount to
make: 10 female leather armor: 0, Price: --"

Notice it has a 10 in it. This value could be a 10, 15, or 20 so I wanted
to do something like:

If A1 contains "*10*" then B1 = "10"
Else if A1 contains "*15*" then B1 = "15"
Else if A1 contains "*20*" then B1 = "20"
End If

Or I could use a Select Case, but I think you see what I am trying to do.
Hope this is enough information

TIA

Wally Steadman
 
Use the instr function and check if it returns a number greater than 0. since
you are fimiliar with if then else and select I will not bore you with the
details...

HTH
 
If Range("A1") Like "*10*" then Range("B1") = "10"
Elseif Range("A1") Like "*15*" then Range("B1") = "15"
Elseif Range("A1") Like "*20*" then Range("B1") = "20"
End If

or

Select Case True
Case Range("A1") Like "*10*" : Range("B1") = "10"
Case Range("A1") Like "*15*" : Range("B1") = "15"
Case Range("A1") Like "*20*" : Range("B1") = "20"
End Select


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Needs a few more lines

If Range("A1") Like "*10*" Then
Range("B1") = "10"
ElseIf Range("A1") Like "*15*" Then
Range("B1") = "15"
ElseIf Range("A1") Like "*20*" Then
Range("B1") = "20"
End If

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Hello all,
I have a text string in cell A1 and I am trying to do an If Statement that
would place a certain value in B1 if something is contained in the text
string. I know this is probably simple, but have looked and I am stumped.

Cell A1 contains the following:
"a bulk order deed blessed small bulk order normal leather normal amount to
make: 10 female leather armor: 0, Price: --"

Notice it has a 10 in it. This value could be a 10, 15, or 20 so I wanted
to do something like:

If A1 contains "*10*" then B1 = "10"
Else if A1 contains "*15*" then B1 = "15"
Else if A1 contains "*20*" then B1 = "20"
End If

Or I could use a Select Case, but I think you see what I am trying to do.
Hope this is enough information

TIA

Wally Steadman
Click to expand...

If the number of interest is always following the first colon, then:

Range("B1") = Val(Mid(Range("A1"), InStr(Range("A1"), ":") + 1, 5))


--ron
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Display entire number in cell 3
Naming non-contigent cells with vba 5
Excel Add ^ to a row of cells containing letters 3
IF - formatting problem 8
Disable/enable or lock/unlock cells in VBA dependent on other cells values 2
How to check if a certain function is used 2
TRIM IF CELL CONTAINS ONE WORD, ABBREVIATE IF IT CONTAINS MULTIPLE WORDS 1
How to dynamically change the list in drop down list based on thevalue of another cell? 1

Back
Top