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

excel vba string worksheet-function
warren picture warren · Nov 4, 2008 · Viewed 48.9k times · Source

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

Joseph Bui picture Joseph Bui · Nov 4, 2008

Try:

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

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

Related questions

Insert characters into string in different locations

I have a string which looks like this 20141007023617. And I would like to make it look like this 2014-10-07T02:36:17Z. The T and Z are very important for me. So I would like to insert them as well. …

Read More
VBA - convert to date

I have one more time a problem: I want to convert from Strings to dates in VBA The Strings look like: YYYY-DD-MM The date should be like: DD.MM.YYYY I know, normally you do this with the method cdate(), …

Read More
display a 2 row message pop -up vba 6

is there a way to display a 2 or 3 or 4 or n line message on a pop-up window in vba 6 ? For the moment my pop-up window ( calling the MsgBox function) displays the message like this : You did something wrong. Please enter …

Read More