cell not equal to two values, return a third value

**acp** · 12-10-2012, 03:37 AM

I'm having a difficult time coming up with what is probably a simple formula for replacing text based on multiple criteria.

In a perfect world, the formula would read something like this:
if A1 does not equal "HD" or "NA", then replace A1 with "SD".

In a less than perfect world, it would look like this:
if A1 does not equal "HD" or "NA", then put "SD" in B1.

(Note that both "not equal to" criteria must be met.)

I know how to accomplish bits and pieces of this task but I don't know how to put it all together to make it work. Any help would be appreciated.

Thanks!

PS: Is it possible to replace a cell's value based on the cell or font color? I was able to create a conditional statement that highlighted all the cells that don't equal "HD" or "NA". I know I can sort by the color and type in "SD" and use fill down or fill across or whatever to change them all, but I'd really like to learn the correct way to do this.

**Fotis1991** · 12-10-2012, 03:47 AM

....In a less than perfect world, it would look like this:

In this non perfect world, using formula in B1!

=if(OR(a1<>"HD",A1<>"NA"),"SD")

For the perfect solution, you'll need VBA.

**Fotis1991** · 12-10-2012, 03:50 AM

Better this one

=IF(OR(A1="HD",A1="NA"),A1,"SD")

**dilipandey** · 12-10-2012, 03:52 AM

Hi acp,

Formula:

=IF(AND(A1<>"HD",A1<>"NA"),"SD","")

Use above in B1 as I belong to "less than perfect world"

Regards,
DILIPandey

<click on below 'star' if this helps>

**acp** · 12-10-2012, 03:32 PM

For the perfect solution, you'll need VBA.

Good heavens! I'm barely able to manage a simple formula, I don't think I'm ready for VBA yet.

Sorry, I didn't even think to mention in my post that HD and NA needed to stay in their cells; I just needed to replace everything else with SD.

Fotis, thanks for the quick replies. The first formula didn't work right; it filled everything in with SD. But I changed the OR to AND and it worked except it filled the non-SD cells in with FALSE. The formula in your second reply was perfect. It filled the correct cells with SD and also left the other text as is.

Dilip, thanks for your reply, also. Because I didn't include all the information in my original post, your formula isn't quite right (it removes HD and NA and leaves the cells blank) for this purpose. But I've already thought of another use for it for another project I'm working on.

Thanks again! You both saved me a bunch of time.