Help with Search and Replace

[Guest]

Hello,

I am looking for a simple method of searching through a range and replacing
target specific characters within each cell / piece of data replacing or
deleting them the old value with a new value. The key is that I want to
specify the position of the character within the cell so I do not overwrite
with the incorrect data.

Thank you very much,
Mark

 

[Don Guillett]

Try this to find the position. Look in vba HELP index
instr(

 

[Guest]

do you mean you wish to specify the postion of the character that is found or
that you are overwritting with? I would say just use specail characters to
identify EXACTLY what you want overwritten

 

[Guest]

If you are looking to replace one substring for another that is the same
length, you can use the "Mid" statement. Mid lets you designate the starting
position and the length of the string you want to work with. For example, if
I wanted to replace the substring "a" with the substring "b" if "a" appeared
as the third character in the string I could use.

Dim strTest As String

strTest = ActiveCell.Formula

If Mid(strTest, 3, 1) = "a" Then
Mid(strTest, 3, 1) = "b"
ActiveCell.Formula = strTest
End If

If the length of the substrings you are finding and replacing are different
lenghts, you should look at the VBA "Replace" function (not to be confused
with the Excel Text function - REPLACE)

Stan Shoemaker
Palo Alto, CA