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

It's probably easier to start at the end, make your additions to the beginning of the string, and only add D if Y is not present.

Answer 2

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.

Answer 3

I guess D could appear anywhere, so how about:

If InStr(strString, "Y") > 0 Then
   strString = Replace(strString, "d", "")
   strString = Replace(strString, "  ", "")
   strString = Replace(strString, " ,", "")
   strString = Replace(strString, ",,", ",")
End If

Answer 4

Try:

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

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

Answer 5

I just go 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))

(update - the if statements were in the wrong order, I just pasted the fixed version in)

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

It looks relatively similar to @Joseph's answer.

Is there a better solution?

Answer 6

If there are not too many of these combinations that you want to remove, you can use =IF(FIND("D"; A2)> 0; REPLACE(A2;1;3;"");A2).

Answer 7

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.