if cell contains.....
 

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

if cell contains.....
 

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

"Wally Steadman" wrote:

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

if cell contains.....
 

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)


"Wally Steadman" wrote in message
...
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

if cell contains.....
 

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)


"Bob Phillips" wrote in message
...
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)


"Wally Steadman" wrote in message
...
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

if cell contains.....
 

On Fri, 31 Dec 2004 14:19:25 -0500, "Wally Steadman"
wrote:

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


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

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


--ron