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)

Solution 1

I just got this as a possible solution via email, too:

=IF(A15<>A14,G15,IF(OR(AND(G15="CR247, ",ISNUMBER(FIND("CR247, ",H14))),AND(G15="CR149, ",ISNUMBER(FIND("CR215, ",H14))),AND(G15="CR149, ",ISNUMBER(FIND("CR180, ",H14))),AND(G15="CR180, ",ISNUMBER(FIND("CR215, ",H14))),G15="CR113, "),H14,G15&H14))

(this has the "real" values with precedence rules)

It looks relatively similar to @Joseph's answer.

Is there a better solution?

Solution 2

Try:

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

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

Solution 3

Firstly, why not keep a string array instead as you go through all the cells, then concatenate it all at the end?

Otherwise, you'll be using string functions like INSTR and MID to do something like:

start1 = instr(myLongString,"Y, ")
if start1 > 0 Then
    start2 = instr(myLongString,"D, ")
    if start2 > 0 then
        newLongString = left(myLongString, start2 - 1) & _
                        mid(myLongString, start2 + 3)
    end if
end if

But, as I said, I would keep an array that is easy to loop through, then once you have all the values you KNOW you will use, just concatenate them at the end.

Solution 4

VBA : You can always use the regexp object. I think that gives you the ability to test anything on your script as long as you build correctly the regular expression.

Check out : http://msdn.microsoft.com/en-us/library/yab2dx62(VS.85).aspx ( for regexp reference )
and a simple tool to test your regexps : http://www.codehouse.com/webmaster_tools/regex/

In-cell: you could do it in a more excel friendly way:
suppose on column A:A you have the values.
You can add a new column where you perform the check
if(indirect("A"&row()) <> indirect("A"&row()-1), indirect("G"&row()), indirect("G"&row())& indirect("H"&row()))
or whatever the values are. I guess however that on one branch of the if statement the value should be blank. After that you concatenate only the B:B column values ( skipping blanks if needed ).

Hope this helps.

View more solutions

Comments

[Lance Roberts]

You need to use ISERROR or ISNUMBER on the FIND statement, incase it doesn't find what it's looking for (and therefore you'll have to switch the two cases around).

[warren]

fwiw: this is the answer I ended-up using - thanks for the other suggestions!!