I went thru your RealSample and found 5 errors (typos) in the Address1 Column
- Doublespaces
- Dash errors (Assuming X-X is desired)
- X- X
- X -X
- X - X
- 360 MAIN ST vs 360 MAIN ST E
In that small sample there were multiple instances of 25 distinct address1's with those errors.
I did not analyze Column Address2 but I did notice a RM 204 vs STE 204 error.
This is using the KISS Principle. It can be sped up using arrays for the OldString and the NewString
Sub FixTypos()
'With Application
'.ScreenUpdating = False
'.DisplayAlerts = False
'End With
With ActiveSheet
'Reset Parameters and replace doublespaces
.Cells.Replace What:=" ", Replacement:=" ", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:= _
False, ReplaceFormat:=False
.Cells.Cells.Replace What:=" -", Replacement:="-"
.Cells.Cells.Replace What:="- ", Replacement:="-"
.Cells.Cells.Replace What:="-", Replacement:="-" 'Style choice
.Cells.Cells.Replace What:="360 MAIN ST", Replacement:="360 MAIN ST E", _
LookAt:=xlWhole 'To avoid 360 MAIN ST EE
End With
With Application
.ScreenUpdating = True
.DisplayAlerts = True
'End With
End Sub
'Cells.Replace What:=OldString, _
Replacement:=NewString, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:= _
False, ReplaceFormat:=False
If you very many specific errors (like Main St E and RM vs STE, You may want to list the errors and corrections on a hidden sheet and create the Arrays from those lists.