| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
IF Statement
IN cell a1 I have CP-Barge
In B1 I want a formula that says IF A1 contains CP then Enter CP into cell B1otherwise leave it Blank I have tried =IF(a1="*CP*","CP","") but it does not work. Thanks for your help |
|
#2
|
|||
|
|||
|
=if(left(a1,2)="CP","CP","")
if A1 always starts with the two characters CP. DME wrote: IN cell a1 I have CP-Barge In B1 I want a formula that says IF A1 contains CP then Enter CP into cell B1otherwise leave it Blank I have tried =IF(a1="*CP*","CP","") but it does not work. Thanks for your help -- Dave Peterson |
|
#3
|
|||
|
|||
|
DME Wrote: IN cell a1 I have CP-Barge In B1 I want a formula that says IF A1 contains CP then Enter CP into cell B1otherwise leave it Blank I have tried =IF(a1="*CP*","CP","") but it does not work. Thanks for your help Will this work for you? =if(left(A1,2)="CP","CP","") -- BenjieLop ------------------------------------------------------------------------ BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019 View this thread: http://www.excelforum.com/showthread...hreadid=273449 |
|
#4
|
|||
|
|||
|
Try this:
=IF(ISERR(SEARCH("*CP*",A1)),"","CP") HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "DME" <craigjoseathotmaildotcom wrote in message ... IN cell a1 I have CP-Barge In B1 I want a formula that says IF A1 contains CP then Enter CP into cell B1otherwise leave it Blank I have tried =IF(a1="*CP*","CP","") but it does not work. Thanks for your help |
|
#5
|
|||
|
|||
|
If you want it case-sensitive, amend RD's formula to
=IF(ISERR(FIND("CP",A1)),"","CP") -- HTH RP "RagDyer" wrote in message ... Try this: =IF(ISERR(SEARCH("*CP*",A1)),"","CP") HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "DME" <craigjoseathotmaildotcom wrote in message ... IN cell a1 I have CP-Barge In B1 I want a formula that says IF A1 contains CP then Enter CP into cell B1otherwise leave it Blank I have tried =IF(a1="*CP*","CP","") but it does not work. Thanks for your help |
|
#6
|
|||
|
|||
|
The problem with using FIND() in this scenario though Bob, is that it
*doesn't* accept wild cards, and to meet the OP's specs, wildcards *are* necessary. -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit! ------------------------------------------------------------------- "Bob Phillips" wrote in message ... If you want it case-sensitive, amend RD's formula to =IF(ISERR(FIND("CP",A1)),"","CP") -- HTH RP "RagDyer" wrote in message ... Try this: =IF(ISERR(SEARCH("*CP*",A1)),"","CP") HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "DME" <craigjoseathotmaildotcom wrote in message ... IN cell a1 I have CP-Barge In B1 I want a formula that says IF A1 contains CP then Enter CP into cell B1otherwise leave it Blank I have tried =IF(a1="*CP*","CP","") but it does not work. Thanks for your help |
|
#7
|
|||
|
|||
|
Why? FIND finds it wherever it is (as does SEARCH). The OP used wildcards,
but I don't see that makes it a requirement. Am I missing something? -- HTH RP "RagDyer" wrote in message ... The problem with using FIND() in this scenario though Bob, is that it *doesn't* accept wild cards, and to meet the OP's specs, wildcards *are* necessary. -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit! ------------------------------------------------------------------- "Bob Phillips" wrote in message ... If you want it case-sensitive, amend RD's formula to =IF(ISERR(FIND("CP",A1)),"","CP") -- HTH RP "RagDyer" wrote in message ... Try this: =IF(ISERR(SEARCH("*CP*",A1)),"","CP") HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "DME" <craigjoseathotmaildotcom wrote in message ... IN cell a1 I have CP-Barge In B1 I want a formula that says IF A1 contains CP then Enter CP into cell B1otherwise leave it Blank I have tried =IF(a1="*CP*","CP","") but it does not work. Thanks for your help |
|
#8
|
|||
|
|||
|
You're not missing anything!
It's me who's missing brain power.<g Funny how that power of suggestion can throw you off. -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit! ------------------------------------------------------------------- "Bob Phillips" wrote in message ... Why? FIND finds it wherever it is (as does SEARCH). The OP used wildcards, but I don't see that makes it a requirement. Am I missing something? -- HTH RP "RagDyer" wrote in message ... The problem with using FIND() in this scenario though Bob, is that it *doesn't* accept wild cards, and to meet the OP's specs, wildcards *are* necessary. -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit! ------------------------------------------------------------------- "Bob Phillips" wrote in message ... If you want it case-sensitive, amend RD's formula to =IF(ISERR(FIND("CP",A1)),"","CP") -- HTH RP "RagDyer" wrote in message ... Try this: =IF(ISERR(SEARCH("*CP*",A1)),"","CP") HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "DME" <craigjoseathotmaildotcom wrote in message ... IN cell a1 I have CP-Barge In B1 I want a formula that says IF A1 contains CP then Enter CP into cell B1otherwise leave it Blank I have tried =IF(a1="*CP*","CP","") but it does not work. Thanks for your help |
|
#9
|
|||
|
|||
|
This is what I need except I needed it nested for several text possibilities.
For example, I need it to find "apples", "oranges", "bananas" or "grapes". I need the wildcards because these words could be anywhere in a cell. "RagDyer" wrote: Try this: =IF(ISERR(SEARCH("*CP*",A1)),"","CP") HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "DME" <craigjoseathotmaildotcom wrote in message ... IN cell a1 I have CP-Barge In B1 I want a formula that says IF A1 contains CP then Enter CP into cell B1otherwise leave it Blank I have tried =IF(a1="*CP*","CP","") but it does not work. Thanks for your help |
|
#10
|
|||
|
|||
|
Hi maryj
if it finds "apples" do you want "apples" in B1; if it finds "oranges" do you want "oranges" in B1 etc? If so then one option is =IF(ISERR(SEARCH("*CP*",A1)),IF(ISERR(SEARCH("*app les*",A1)),IF(ISERR(SEARCH("*oranges*",A1)),IF(ISE RR(SEARCH("*bananas*",A1)),IF(ISERR(SEARCH("*grape s*",A1)),"","grapes"),"bananas"),"oranges"),"apple s"),"CP") Cheers JulieD "maryj" wrote in message ... This is what I need except I needed it nested for several text possibilities. For example, I need it to find "apples", "oranges", "bananas" or "grapes". I need the wildcards because these words could be anywhere in a cell. "RagDyer" wrote: Try this: =IF(ISERR(SEARCH("*CP*",A1)),"","CP") HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "DME" <craigjoseathotmaildotcom wrote in message ... IN cell a1 I have CP-Barge In B1 I want a formula that says IF A1 contains CP then Enter CP into cell B1otherwise leave it Blank I have tried =IF(a1="*CP*","CP","") but it does not work. Thanks for your help |
|
#11
|
|||
|
|||
|
Perfect!! That's just what I needed.
"JulieD" wrote: Hi maryj if it finds "apples" do you want "apples" in B1; if it finds "oranges" do you want "oranges" in B1 etc? If so then one option is =IF(ISERR(SEARCH("*CP*",A1)),IF(ISERR(SEARCH("*app les*",A1)),IF(ISERR(SEARCH("*oranges*",A1)),IF(ISE RR(SEARCH("*bananas*",A1)),IF(ISERR(SEARCH("*grape s*",A1)),"","grapes"),"bananas"),"oranges"),"apple s"),"CP") Cheers JulieD "maryj" wrote in message ... This is what I need except I needed it nested for several text possibilities. For example, I need it to find "apples", "oranges", "bananas" or "grapes". I need the wildcards because these words could be anywhere in a cell. "RagDyer" wrote: Try this: =IF(ISERR(SEARCH("*CP*",A1)),"","CP") HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "DME" <craigjoseathotmaildotcom wrote in message ... IN cell a1 I have CP-Barge In B1 I want a formula that says IF A1 contains CP then Enter CP into cell B1otherwise leave it Blank I have tried =IF(a1="*CP*","CP","") but it does not work. Thanks for your help |
|
#12
|
|||
|
|||
|
you're welcome and thanks for the feedback
"maryj" wrote in message ... Perfect!! That's just what I needed. "JulieD" wrote: Hi maryj if it finds "apples" do you want "apples" in B1; if it finds "oranges" do you want "oranges" in B1 etc? If so then one option is =IF(ISERR(SEARCH("*CP*",A1)),IF(ISERR(SEARCH("*app les*",A1)),IF(ISERR(SEARCH("*oranges*",A1)),IF(ISE RR(SEARCH("*bananas*",A1)),IF(ISERR(SEARCH("*grape s*",A1)),"","grapes"),"bananas"),"oranges"),"apple s"),"CP") Cheers JulieD "maryj" wrote in message ... This is what I need except I needed it nested for several text possibilities. For example, I need it to find "apples", "oranges", "bananas" or "grapes". I need the wildcards because these words could be anywhere in a cell. "RagDyer" wrote: Try this: =IF(ISERR(SEARCH("*CP*",A1)),"","CP") HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "DME" <craigjoseathotmaildotcom wrote in message ... IN cell a1 I have CP-Barge In B1 I want a formula that says IF A1 contains CP then Enter CP into cell B1otherwise leave it Blank I have tried =IF(a1="*CP*","CP","") but it does not work. Thanks for your help |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel Report Using IF Statement? | Excel Discussion (Misc queries) | |||
| IF Statement with Average Function results in #Value! | Excel Discussion (Misc queries) | |||
| when writing an IF statement what is the syntax for "Not Equal to. | Excel Discussion (Misc queries) | |||
| Combining SUM Function with Nested If Statement | Excel Discussion (Misc queries) | |||
| Macro and If Statement | Excel Discussion (Misc queries) | |||
Linear Mode
