Remove apartment from field

[aschicago]

Good morning. I have an file with in Excel 2003 with over 16,000 address records. Some of the records have an apartment number and some do not. I want to remove the apartment number, if it exists, from the field leaving just the address. The three examples of address type are:

2102 SPRING LN APT C
2104 SPRING LN APT
2106 SPRING LN

So the end result I am looking for is:
2102 SPRING LN
2104 SPRING LN
2106 SPRING LN

Thanks in advance for any help. I am familiar with formulas in Excel but not macros.

 

[texasalynn]

Welcome to MrExcel board...

try this

Sub RemoveAPT()
Dim LR As Long, i As Long
Dim s As String
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row 'get last row column A
    
For i = 2 To LR
    s = LCase(Cells(i, 1))
    If InStrRev(s, "apt") > 0 Then
        Cells(i, 1) = StrConv(Left(s, InStrRev(s, "apt") - 2), vbUpperCase)
    End If
Next i
End Sub

 

[Armando Montes]

Try Find and Replace