String manipulation with Excel - how to remove part of a string if another part is there?

Question

I've done some Googling, and can't find anything, though maybe I'm just looking in the wrong places. I'm also not very adept at VBA, but I'm sure I can figure it out with the right pointers :)

I have a string I'm building that's a concatenation of various cells, based on various conditions. I hit these in order.

=IF(A405<>A404,G405,G405&H404)

What I want to do is go back through my concatenated list, removing a superseded value if the superseder is in the list.

For example, see the following list:

A, D, G, Y, Z

I want to remove D if and only if Y is present.

How would I go about this? (VBA or in-cell, though I'd prefer in-cell)

Answer 1

Try:

=IF(ISERROR(FIND("Y",A1)),A1,SUBSTITUTE(A1,"D, ",""))

But that assumes you always have the comma and space following the D.