PDA

View Full Version : How to find and replace at once?



clif
12-13-2012, 05:49 AM
AaA
AbA
AcA
AdA

How to find and replace at once

AeA
AeA
AeA
AeA

Thanks!

mancubus
12-13-2012, 07:05 AM
if you want to change 1 single character between 2 capitol A's, then you may use Find and Replace by pressing Ctrl+H keys.

make sure you check "Match Case" from Options for case sensitivity.

Find what: A?A
Replace with: AeA

then hit Replace All button.

clif
12-13-2012, 08:15 AM
from

AaB
AbC
AcD

to

AeB
AeC
AeD

GreenDR
12-13-2012, 08:46 AM
are all your "replacable" characters the 2nd characters of the string in the cell?

clif
12-13-2012, 08:49 AM
yes

GreenDR
12-13-2012, 08:53 AM
try this code which replaces all values in column A

Sub test()
Dim s As String
Dim s1 As String
Dim ln As Integer
Dim rws As Long, i As Long
rws = Application.WorksheetFunction.CountA(Range("A:A"))
For i = 1 To rws
s = Cells(i, 1).Value
ln = Len(s)
s1 = Left(s, 1) & "e" & Right(s, ln - 2)
Cells(i, 1).Value = s1
Next
End Sub

mancubus
12-13-2012, 09:03 AM
solution with REPLACE function in helper column.

=REPLACE(A1,2,1,"e")

copied down to last row with data in column A.