How to find and replace characters within a cell

[subzeroheart]

Please help me.

I have an address in a cell for example - [TABLE="width: 193"]
<tbody>[TR]
[TD="width: 193"]2215 Nw 36th St

I need to search and find only Nw and change it to NW.

The same goes for Dr, Us, Ne, St, Ste, Blvd and Dr

How do I accomplish this in a macro?

Thank you for your help in advance[/TD]
[/TR]
</tbody>[/TABLE]

 

[hateme28]

Sub sample()
Dim x(1 To 10) As Variant
x(1) = "Nw"
x(2) = "Dr"
x(3) = "Us"
x(4) = "Ne"
x(5) = "St"
x(6) = "Ste"
x(7) = "Blvd"
For i = 1 To 7
    Range("a1").Value = Replace(Range("a1").Value, x(i), UCase(x(i)))
Next
End Sub

 

[JoeMo]

Please help me.

I have an address in a cell for example - [TABLE="width: 193"]
<tbody>[TR]
[TD="width: 193"]2215 Nw 36th St

I need to search and find only Nw and change it to NW.

The same goes for Dr, Us, Ne, St, Ste, Blvd and Dr

How do I accomplish this in a macro?

Thank you for your help in advance[/TD]
[/TR]
</tbody>[/TABLE]

Welcome to the board.

Select all the cells you want to change, then run this macro.

Sub ReplaceList()
Dim myList As Variant
myList = Array("Nw", "Dr", "Us", "Ne", "St", "Ste", "Blvd")
With Selection
    For i = LBound(myList) To UBound(myList)
        .Replace what:=myList(i), replacement:=UCase(myList(i)), lookat:=xlPart, MatchCase:=False
    Next i
End With
End Sub