Originally Posted by isabelle

hi mb6g,


Sub Macro1()
Dim x1 As Boolean, x2 As Boolean, x3 As Boolean
Dim i As Integer, w As String, c As Range

For Each c In Range("A1:A10") 'range of cells to be determined
For i = Len(c) To 1 Step -1
On Error Resume Next
x1 = Mid(c, i, 1) = "A"
If Err.Number < 0 Then Err.Clear: x1 = False
x2 = IsNumeric(Mid(c, i - 1, 1))
If Err.Number < 0 Then Err.Clear: x2 = False
x3 = IsNumeric(Mid(c, i - 2, 1))
If Err.Number < 0 Then Err.Clear: x3 = False

If x1 And x2 And x3 Then
w = Mid(c, i - 2, 1) & Mid(c, i - 1, 1) & Mid(c, i, 1)
c.Replace What:=w, Replacement:="20A", LookAt:=xlPart
End If
Next
Next
End Sub

--
isabelle



Le 2012-06-08 16:25, mb6g a écrit :
Hi,

I'm trying to find cells that contain a number and the letter A (such as
"16A"), and replace them with just the number part (such as "16").
There are other cells that contain only letters (like "CAT") or only
numbers (like "16") that I don't want to affect. Any advice would be
appreciated.

Thanks!