SUBSTITUTE nested within IF

**malibu06** · 10-22-2010, 12:40 PM

Hi,

I'm trying to SUBSTITUTE one of two text values depending on what is in the cell to the left.

Formula I've tried is:

=IF(SEARCH("XXXX",B1),SUBSTITUTE(B1,$G$1,$H$1),(SEARCH("YYYY",B1),SUBSTITUTE(B1,$G$2,$H$2)))

Each half works separately

=IF(SEARCH("XXXX",B1),SUBSTITUTE(B1,$G$1,$H$1)

or

=IF(SEARCH("YYYY",B1),SUBSTITUTE(B1,$G$2,$H$2)

but when I try to combine them I cannot get it to work.

Any ideas?

Thanks

**squiggler47** · 10-22-2010, 01:01 PM

=if(search("xxxx",b1),substitute(b1,$g$1,$h$1),if(search("yyyy",b1),substitute(b1,$g$2,$h$2),"")

**DonkeyOte** · 10-22-2010, 01:05 PM

The approach will return an error if "XXXX" is not found - you would want to encase within an appropriate handler (eg ISNUMBER)

**squiggler47** · 10-22-2010, 01:14 PM

Yes it might be better to know what is in the cells and what the strings are!

I just provided the joining, as to better formula that wasnt asked, but with more details there may be a better way!

**DonkeyOte** · 10-22-2010, 01:17 PM

What I meant by my post was: the formula won't work / not fit for purpose
(not that it could be improved upon)

**malibu06** · 01-18-2011, 11:57 PM

Solved. Sorry for the delayed reply. Thanks all!

SUBSTITUTE nested within IF

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

SUBSTITUTE nested within IF

Re: SUBSTITUTE nested within IF

Re: SUBSTITUTE nested within IF

Re: SUBSTITUTE nested within IF

Re: SUBSTITUTE nested within IF

Re: SUBSTITUTE nested within IF [SOLVED]

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions