Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing Options | Excel Discussion (Misc queries) | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing | Excel Discussion (Misc queries) | |||
Populate a cell if values in cell 1 and cell 2 match cell 3 and 4 | Excel Worksheet Functions | |||
How to create/run "cell A equals Cell B put Cell C info in Cell D | Excel Discussion (Misc queries) | |||
Question: Cell formula or macro to write result of one cell to another cell | Excel Programming |