SUBSTITUTE multiple text strings

  • Thread starter Thread starter richzip
  • Start date Start date
R

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?
 
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
 
it isn't case sensitive, so to replace both upper and lower case, use 4
levels of nesting.
Click to expand...

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
 
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?
Click to expand...

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

If it is less than eight, you can nest SUBSTITUTE functions.
--ron
 
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!
 
thanks everyone ..it worked perfectly!

T. Valko said:
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!
Click to expand...
 
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!
Click to expand...

I can't imagine not using VBA if I had something that complex.
--ron
 
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
 
Back
Top