ExcelBanter - IF Statement

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - IF Statement (https://www.excelbanter.com/excel-worksheet-functions/5272-if-statement.html)

DME

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

Dave Peterson

=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

BenjieLop

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

RagDyer

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

Bob Phillips

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

RagDyer

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

Bob Phillips

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

RagDyer

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

maryj

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

JulieD

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

maryj

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

JulieD

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

All times are GMT +1. The time now is 01:32 PM.