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?
|