You are here:

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.

Sub delonteReplacecasesensitive()
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
         Exit For
         End If
         If S(i) = c.Value & P(j) Then
         S(i) = c.Offset(0, 1).Value & P(j)
         Exit For
         End If
         Next j
       Next i
       cel.Value = Trim(Join(S, " "))
       Erase S
   Next cel
Next c
Application.ScreenUpdating = True
End Sub

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

Find strings
Find strings  
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.

Thanks again

Hi Sofia,

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 and mention "AllExperts" in the subject line.

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts




I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.


I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2017 All rights reserved.