SUBSTITUTE multiple text strings

[richzip]

I have a column of concatenated text, and would like to add another column
that "deletes" certain strings of characters if it is found in the
concatenated column.
For example, if text strings of "xx" or "yy" are found, I want those
characters to be deleted. So, WWXX would just show as WW; YYZZ would just
show as ZZ.

I found I can use the substitute function to search for just one string:
=SUBSTITUTE(A2,"XX,""). Can I modify this formula to perform the
replacement if XX or YY is found?

 

[LenB]

Looks like this works. The second SUBSTITUTE function passes its result
to the previous one, kinda like an OR.

=SUBSTITUTE(SUBSTITUTE(A2,"XX",""),"YY","")

You can probably nest as many as needed. I didn't test for limits. For
example, it isn't case sensitive, so to replace both upper and lower
case, use 4 levels of nesting.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"yy",""),"xx",""),"XX",""),"YY","")

Len

 

[T. Valko]

it isn't case sensitive, so to replace both upper and lower case, use 4

levels of nesting.

I think you meant it is case sensitive (which it is) but you don't need to
nest 4 levels.

Try it like this:

=SUBSTITUTE(SUBSTITUTE(UPPER(A2),"XX",""),"YY","")

Also note that if the string is XXXZZZ

The result will be XZZZ

 

[Ron Rosenfeld]

I have a column of concatenated text, and would like to add another column
that "deletes" certain strings of characters if it is found in the
concatenated column.
For example, if text strings of "xx" or "yy" are found, I want those
characters to be deleted. So, WWXX would just show as WW; YYZZ would just
show as ZZ.

I found I can use the substitute function to search for just one string:
=SUBSTITUTE(A2,"XX,""). Can I modify this formula to perform the
replacement if XX or YY is found?

How many different types of strings will need to be deleted?

If it is less than eight, you can nest SUBSTITUTE functions.
--ron

 

[T. Valko]

If it is less than eight, you can nest SUBSTITUTE functions.

If you're using Excel 2007 you can nest up to 64 levels plus the top level =
65.

One of these days I'm going to try that out just to see what it looks like!

 

[richzip]

thanks everyone ..it worked perfectly!

If you're using Excel 2007 you can nest up to 64 levels plus the top level =
65.

One of these days I'm going to try that out just to see what it looks like!

 

[Ron Rosenfeld]

If you're using Excel 2007 you can nest up to 64 levels plus the top level =
65.

One of these days I'm going to try that out just to see what it looks like!

I can't imagine not using VBA if I had something that complex.
--ron

 

[LenB]

Sigh, yes you are right, I meant that it is case sensitive. Thanks for
th correction. I tried to point it out only because the OP mentioned
"xx" in one sentence, then showed "XX" in his example. I like your way
using UPPER, as long as it is ok to change the result to upper case.

Len