Macro to find/replace cells containing a number and letter
 

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!

Macro to find/replace cells containing a number and letter
 

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!

Hi Isabelle,

Thanks, that worked great. I took out the x3 requirement, since some of my numbers only have one digit (such as 2A), and I simplified the replace line to:

If x1 And x2 Then c.Replace What:="A", Replacement:="", LookAt:=xlPart.

Thanks again for your help!

Mary