Excel/Find and replace words in sentences excel
QUESTION: Hi Gulshan,
I hope to get the answer from you. Thanks in advance. I have the below code for finding and replacing words in sentences however it does search in the same sheet column D but I want the code to search sheet 2 column D and replace column E words in sheet 1 column B. Hope that makes sense. Basically I don't know how to modify sheet to search column D in sheet 2 and not the same sheet.
Dim c As Range, cel As Range, S As Variant, P As Variant, i As Long, j As Long
P = Array(",", ":", ";", "?", "!") 'Add any other punctuation marks that your text may contain
Application.ScreenUpdating = False
For Each c In Range(Cells(1, "D"), Cells(Rows.Count, "D").End(xlUp))
For Each cel In Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns("B:B")).Cells
S = Split(cel.Value, " ")
For i = LBound(S) To UBound(S)
For j = LBound(P) To UBound(P)
If S(i) = c.Value Then
S(i) = c.Offset(0, 1).Value
If S(i) = c.Value & P(j) Then
S(i) = c.Offset(0, 1).Value & P(j)
cel.Value = Trim(Join(S, " "))
Application.ScreenUpdating = True
ANSWER: Hi Sofia,
I'm sorry I don't understand the question.
Can you be more clear on exactly what you are looking for?
---------- FOLLOW-UP ----------
QUESTION: Hi Gulshan,
Sorry for not making myself clear. I have solved the previous problem and I have similar kind of issue with matching strings. Please if you could help me on this one I will really appreciate it.
Example: Sheet 1 in column B the first entry "ABC THY alina tim tomorrow" so for this entry the code should search the entire column A of sheet 2, if the words are found which is "Tim, Alina" but if any of these found words are present in sheet 3 which is alina then the output should be just "Tim". Hope It makes thins more clear.
I'm sorry, I'm still not able to understand the question.
I'm not sure what you are trying to achieve with this exercise. If I can understand the end objective better, I'll be able to provide a better solution.
If required, please drop me a mail with the excel file. Maybe that will help us work better.
You can send the mail to email@example.com and mention "AllExperts" in the subject line.