if command

[Adrian1]

Hi,

I need to a formula to give a specific value to a cell.

Cell A2 is blank

I want a formula in cell C1 to add the the text "Quote" in cell A2 if cell A1 has the text "HH" or "NHH" contained in it and if A1 doesn't have "HH" or "NHH" in it then A2 needs to stay blank.

Thank you in advance to anyone who can help.

 

[onlyadrafter]

Hello,

Dont understand why you want the formula in C1.

Why not just put this formula in A2?

=IF(OR(A1="NN",A1="NHH"),"Quote","")

Is this no good?

 

[Aladin Akyurek]

...

I need to a formula to give a specific value to a cell.

Cell A2 is blank

I want a formula in cell C1 to add the the text "Quote" in cell A2 if cell A1 has the text "HH" or "NHH" contained in it and if A1 doesn't have "HH" or "NHH" in it then A2 needs to stay blank...

A formula in C1 cannot add the text "Quote" or anything else for that matter to A2.

 

[Adrian1]

Hi

That works if A1 is NHH or HH, but there are other combinations that need to be covered. Eg, A1 may have the text HH 12 MONTH in it (in which case A2 needs to be "quote" or A1 may also have NHH 24 MONTH in it and so on. I need the formula to determine whether or not any part of cell A1 has NHH or HH contained in it.

Sorry, I wasn't very clear in the first instance.

 

[just_jon]

The problem here is that the only ways to put things into cell A2 are --

A] by formulas in A2, or
B] via VBA, or
C] artificially, through formatting

Note tat with C], the "added" bit looks as if it is there but is not part of the cell alue.

 

[fairwinds]

Try:

=IF(ISNUMBER(SEARCH("HH",A1)),"QUOTE","")

That will find HH wherever it is in the text.

 

[onlyadrafter]

Hello,

Try this formula

=IF(ISERROR(FIND("NN",A1)),IF(ISERROR(FIND("NHH",A1)),""),"Quote")

is this working OK?

 

[Adrian1]

excellent, that works fine. Thanks a lot!