Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 74
Default 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?
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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:

Formula:
=SUBSTITUTE(SUBSTITUTE(UPPER(A2),"XX",""),"YY",""
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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 25
Default 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?

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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?



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default 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


  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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



  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 74
Default 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




  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default 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
  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 25
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I substitute text for numbers Eric Excel Discussion (Misc queries) 1 April 20th 07 12:40 AM
Searching for multiple strings and return multiple solutions Ron Excel Worksheet Functions 2 October 6th 06 05:29 PM
add and subtract multiple text strings. Tomkat743 Excel Discussion (Misc queries) 1 March 29th 06 01:54 PM
searching for multiple text strings eddie Excel Discussion (Misc queries) 4 April 10th 05 10:15 PM
How to make a cell recognize multiple text strings? Tourcat Excel Worksheet Functions 1 February 8th 05 09:29 PM


All times are GMT +1. The time now is 12:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"