Replacing directionals at the end of street addresesses

[Devicef]

Hey,

I am trying to find and replace the direction in street addresses (Se, Ne, Nw, Sw) with the capitalized version of them after they have been propered (=proper(X)). This is easy if they are in the middle (below), but I don't know how to make it correct ones at the end of an address, ex 1234 Main St Se, since it doesn't have a space after it. These are in full street addresses and some with have " Se", ex Seasons Drive, in them so I can't do it that way.

Please Login or Register  to view this content.

Is there a way to make VBA look for the end of the cell? So instead of the 2nd space, it would look for that. Like " Ne[end of cell]"

Thank you!

[KOKOSEK]

For i = 1 To Cells(Rows.Count, "F").End(xlUp).Row
Range("F" & i).Value = Left(Range("F" & i).Value, Len(Range("F" & i).Value) - 2) & " " & UCase(Right(Range("F" & i).Value, 2))
Next i

[JBeaucaire]

You can add a space to the end for the function, then trim the results.

This seems like a workable approach. Also, we're only going to read the data once, then write it out to the sheet once at the end:

Please Login or Register  to view this content.

This way it's nearly instantaneous, even if there were a million rows.