Partial deletion

[ExcelVixen]

Hi. I am trying to delete a word in a string in column A, if the word is found in column B. how do i delete only the specific word found in the string and not the entire string in the cell? E.g. Cell A1 = The cat ate the rat ; Cell B1 = rat. I just want to delete the word "Rat" in A1. A1 result = The cat ate the ; The values differ in each cell in both columns. see image attached.

I have tried find and replace but it is tedious for thousands of rows of data.
thanking you in advance.
N

 

[Peter_SSs]

Welcome to the MrExcel board!

Can you confirm the expected outcomes below
Row 2: The and the do not match exactly
Row 3: rat is inside bratty

22 07 21.xlsm
	A	B
1
2	The cat sat	the
3	Tom was bratty	rat
Sheet2 (3)

 

[ExcelVixen]

Hi. it must not be a partial recognition. "Rat" inside of "bratty" must be ignored. the case is irrelevant.

 

[Peter_SSs]

Here are a few possible options.

1. Formula as in column C below. Some possible 'case' issues in the results? (eg C4 and C6)
2. IF you have the TEXTSPLIT function (not too many users do have it yet) then column D formula.
3. This user-defined function below and as used in column E. Post back if you want to investigate this option further and don't know how.

Function SubWord(orig As String, subs As String) As String
  With CreateObject("VBScript.RegExp")
    .IgnoreCase = True
    .Pattern = "\b" & subs & "\b"
    SubWord = Application.Trim(.Replace(orig, ""))
  End With
End Function

ExcelVixen.xlsm
	A	B	C	D	E
1	Original	Word	Result	Result	Result
2	the cat ate the rat	rat	the cat ate the	the cat ate the	the cat ate the
3	the house is blue	blue	the house is	the house is	the house is
4	Once upon a time the	the	once upon a time	Once upon a time	Once upon a time
5	The cat sat	the	cat sat	cat sat	cat sat
6	Tom was bratty	rat	tom was bratty	Tom was bratty	Tom was bratty
Substitute

Cell Formulas
Range	Formula
C2:C6	C2	=TRIM(SUBSTITUTE(" "&LOWER(A2)&" "," "&LOWER(B2)&" "," "))
D2:D6	D2	=LET(ts,TEXTSPLIT(A2," "),TEXTJOIN(" ",1,FILTER(ts,ts<>B2)))
E2:E6	E2	=SubWord(A2,B2)

 

[ExcelVixen]

Here are a few possible options.

1. Formula as in column C below. Some possible 'case' issues in the results? (eg C4 and C6)
2. IF you have the TEXTSPLIT function (not too many users do have it yet) then column D formula.
3. This user-defined function below and as used in column E. Post back if you want to investigate this option further and don't know how.

Function SubWord(orig As String, subs As String) As String
  With CreateObject("VBScript.RegExp")
    .IgnoreCase = True
    .Pattern = "\b" & subs & "\b"
    SubWord = Application.Trim(.Replace(orig, ""))
  End With
End Function

ExcelVixen.xlsm
	A	B	C	D	E
1	Original	Word	Result	Result	Result
2	the cat ate the rat	rat	the cat ate the	the cat ate the	the cat ate the
3	the house is blue	blue	the house is	the house is	the house is
4	Once upon a time the	the	once upon a time	Once upon a time	Once upon a time
5	The cat sat	the	cat sat	cat sat	cat sat
6	Tom was bratty	rat	tom was bratty	Tom was bratty	Tom was bratty
Substitute

Cell Formulas
Range	Formula
C2:C6	C2	=TRIM(SUBSTITUTE(" "&LOWER(A2)&" "," "&LOWER(B2)&" "," "))
D2:D6	D2	=LET(ts,TEXTSPLIT(A2," "),TEXTJOIN(" ",1,FILTER(ts,ts<>B2)))
E2:E6	E2	=SubWord(A2,B2)

thank you. it works brilliantly.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

As a matter of interest, which one suits you the best?