hi all

i need to replace the string in column c. For ex the string DOOR INSTL
should be converted to DOOR INSTALATION. DOOR ASSY to convert it to
DOOR ASSEMBLY

i have written a code which changes INSTL to INSTALLATION and so on.
i have huge list of these conversion

My problem is that have written thin in code.

Can we have a code which will refer to sheet 2 and replace a part of
the text string ?

Code is as below.



For new word start copy of the below block of code
If (InStr(1, .Value, " INSTL ", vbTextCompare) 0) Then
'Please assign the new word here with leading and
trailing whitespace
myWord = " INSTL "
.Characters(Start:=InStr(1, .Value, myWord,
vbTextCompare), Length:=Len(myWord)).Text = " INSTALLATION "
ElseIf (InStr(1, .Value, "INSTL ", vbTextCompare) 0)
Then
'Please assign the new word here with trailing white
space
myWord = "INSTL "
intStart = InStr(1, .Value, myWord, vbTextCompare) - 1
If intStart = 0 Then
.Characters(Start:=InStr(1, .Value, myWord,
vbTextCompare), Length:=Len(myWord)).Text = "INSTALLATION "
End If
ElseIf (InStr(1, .Value, " INSTL", vbTextCompare) 0)
Then
'Please assign the new word here with leading white
space
myWord = " INSTL"
intStart = InStr(1, .Value, myWord, vbTextCompare) +
Len(myWord) - 1
intCellValLength = Len(rCell.Value)
If (intCellValLength = intStart) Then
.Characters(Start:=InStr(1, .Value, myWord,
vbTextCompare), Length:=Len(myWord)).Text = " INSTALLATION"
End If
ElseIf (InStr(1, UCase(.Value), "INSTL", vbTextCompare)
0) Then
'Please assign the new word here without leading and
trailing white space
myWord = "INSTL"
If (UCase(rCell.Value) = myWord) Then
.Characters(Start:=InStr(1, .Value, myWord,
vbTextCompare), Length:=Len(myWord)).Text = "INSTALLATION"
End If
End If