Substitute All Except

[JRDunya]

Hi, everyone,

Is there any way to substitute a character or series of characters from a cell with other characters, but with some exceptions? For example, substitute (or replace) all instances of "X" in cell B1 by cell A1, except "XP", "Xbox", "Xochitl", "Xmas", and a few others. I had written =Substitute(B1, "X", A1) but then not sure how to add the exceptions there. Perhaps adding a nested formula in place of B1? Or is there another function for that? I'm also trying to avoid VBA. So not sure if that's possible.

I would appreciate any help or advice with this. Thank you.

 

[Marcílio_Lobão]

JRDunya, Good morning.

Try to use:

=Substitute(B1, " X ", A1)

Please, tell us if it worked for you.

I hope it helps.

 

[jasonb75]

Are the X's to be substituted part of other words or are they surrounded by spaces? (If so then the suggestion in post 2 should work but will need editing to allow for the X being at the beginning or end of the cell).

Without seeing an example of the cell content, there could be a lot more wrong answers than right ones.

 

[JRDunya]

Thank you to both. I meant to say that I had already tried

=SUBSTITUTE(B1,"X",A1)

but not sure what to do next to add exceptions.

There are no white spaces before or after "X"

Below is what I have. As you can see X is always substituted.

Substitute All Except.xlsx
	A	B	C
1	apples	There are 10 X.	There are 10 apples.
2	bananas	5 X in the fridge.	5 bananas in the fridge.
3	oranges	We love X specially in Xmas	We love oranges specially in orangesmas
4	pineapples	I used to have a background with X on my old Windows XP operating system.	I used to have a background with pineapples on my old Windows pineapples P operating system.
5	strawberries	Where are the X?	Where are the strawberries?
Sheet1

Cell Formulas
Range	Formula
C1:C5	C1	=SUBSTITUTE(B1,"X",A1)

Basically X should be substituted by A1 whether there is blank space or special character next to it, but not when there's an alphanumeric character.

 

[jasonb75]

You wouldn't be able to add exceptions, you would need to test for each special character like below.

If there is only ever 1 X to be replaced in each cell then it might be possible to simplify the formula,

Book1
	A	B	C
1	apples	There are 10 X.	There are 10 apples.
2	bananas	5 X in the fridge.	5 bananas in the fridge.
3	oranges	We love X specially in Xmas	We love oranges specially in Xmas
4	pineapples	I used to have a background with X on my old Windows XP operating system.	I used to have a background with pineapples on my old Windows XP operating system.
5	strawberries	Where are the X?	Where are the strawberries?
Sheet1

Cell Formulas
Range	Formula
C1:C5	C1	=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" "&B1," X "," "&A1&" ")," X."," "&A1&".")," X?"," "&A1&"?"))

 

[JRDunya]

Thank you for your suggestion, jasonb75. Although there may be cases where more than one X appear within the same cell, the TRIM formula you have suggested opens up possibilities for other different projects. Thank you for that. But for this case where more than X may appear, I guess I'll have to go through the lengthy process of writing a SUBSTITUTE formula for each non-alphanumeric possible combination, i.e.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,"X ",A1&" "),"X!",A1&"!"),"X;",A1&";"),"X-",A1&"-"),"X.",A1&"."),"X?",A1&"?")

This is an example of X and a couple of special character combinations, the real formula would look a lot longer for each non-alphanumeric possible combination.

I'll see if there's still another more effective work around to avoid going that way.

 

[jasonb75]

I'll see if there's still another more effective work around to avoid going that way.

With formulas, there isn't one, you would need to use vba instead. With something like this, it is always better to set up with a non standard character to replace, although that may not always be possible.

Also, you need to use the method that I suggested with TRIM and the space in front of the X if you want accuracy. Your way of doing it would still replace an X at the end of a word, 'FED-EX' for example.

The substitute function is case sensitive so a lower case x would not be replaced.

 

[Rick Rothstein]

Here is a UDF (user defined function) that will replace standalone (non-imbedded) case-sensitive text strings with a specified text string. Its first argument is the text to operate on, the second argument is the case-sensitive text to find and the third (last) argument is the text to replace it with.

Function SubAll(ByVal Txt As String, FindWhat As String, ReplaceWith As Variant) As String
  Dim X As Long
  For X = 1 To Len(Txt) + Len(ReplaceWith)
    If Mid(" " & Txt & " ", X, Len(FindWhat) + 2) Like "[!A-Za-z0-9]" & FindWhat & "[!A-Za-z0-9]" Then
      Txt = Application.Replace(Txt, X, Len(FindWhat), ReplaceWith)
    End If
  Next
  SubAll = Txt
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use SubAll just like it was a built-in Excel function. For example (using your posted sample),

=SubAll(B1,"X",A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Bo_Ry]

Please try

Book1
	A	B	C
1	apples	There are 10 X.	There are 10 apples.
2	bananas	5 X in the fridge.	5 bananas in the fridge.
3	oranges	We love X specially in Xmas	We love oranges specially in Xmas
4	pineapples	I used to have a background with X on my old Windows XP operating system.	I used to have a background with pineapples on my old Windows XP operating system.
5	strawberries	Where are the X?	Where are the strawberries?
Sheet1

Cell Formulas
Range	Formula
C1:C5	C1	=SUBSTITUTE(B1,"X",A1,MATCH(1,EXACT(LOWER(MID(FILTERXML("<x><m>"&SUBSTITUTE(B1," ","</m><m>")&"</m></x>","//m[string-length(.) < 3 and contains(., 'X')]"),2,1)),UPPER(MID(FILTERXML("<x><m>"&SUBSTITUTE(B1," ","</m><m>")&"</m></x>","//m[string-length(.) < 3 and contains(., 'X')]"),2,1)))+{0},))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Rick Rothstein]

@Bo_Ry... Just noting that your formula only finds the first "X" in a cell but in Message #6 the OP said "Although there may be cases where more than one X appear within the same cell". Also, if there is no "X" in the cell, your formula returns a #N/A error.