SUBSTITUTE multiple text strings
 

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?

Answer: SUBSTITUTE multiple text strings
 

Yes, you can modify the formula to perform the replacement if XX or YY is found by using the nested SUBSTITUTE function. Here's how you can do it:

1. Assuming your concatenated text is in column A, enter the following formula in cell B2:
   
   
   Formula:

   ---

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


   ---

2. This formula uses the SUBSTITUTE function twice, with the second SUBSTITUTE nested inside the first one. The first SUBSTITUTE replaces "XX" with an empty string ("") in the original text, and the second SUBSTITUTE replaces "YY" with an empty string in the result of the first SUBSTITUTE.
   
3. Copy the formula down to the rest of the cells in column B to apply it to the entire column.
   
4. The result will be a new column (column B) with the modified text strings that have "XX" or "YY" removed.

Note that this formula is case-sensitive, so it will only remove "XX" and "YY" if they are in uppercase. If you want to remove them regardless of case, you can use the UPPER function to convert the text to uppercase before applying the SUBSTITUTE function. For example:

This formula first converts the original text to uppercase using the UPPER function, and then applies the nested SUBSTITUTE functions to remove "XX" and "YY" regardless of case.

SUBSTITUTE multiple text strings
 

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,"y y",""),"xx",""),"XX",""),"YY","")

Len

richzip wrote:
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?

SUBSTITUTE multiple text strings
 

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


--
Biff
Microsoft Excel MVP


"LenB" wrote in message
...
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,"y y",""),"xx",""),"XX",""),"YY","")

Len

richzip wrote:
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?

SUBSTITUTE multiple text strings
 

On Wed, 30 Jan 2008 20:26:10 -0800, richzip
wrote:

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

SUBSTITUTE multiple text strings
 

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!

--
Biff
Microsoft Excel MVP


"Ron Rosenfeld" wrote in message
...
On Wed, 30 Jan 2008 20:26:10 -0800, richzip

wrote:

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

SUBSTITUTE multiple text strings
 

thanks everyone ..it worked perfectly!

"T. Valko" wrote:

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!

--
Biff
Microsoft Excel MVP


"Ron Rosenfeld" wrote in message
...
On Wed, 30 Jan 2008 20:26:10 -0800, richzip

wrote:

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

SUBSTITUTE multiple text strings
 

On Thu, 31 Jan 2008 12:50:12 -0500, "T. Valko" wrote:

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!

--
Biff
Microsoft Excel MVP

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

SUBSTITUTE multiple text strings
 

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

T. Valko wrote:
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