Deleting duplicate house numbers

[Sarah]

Hello,

we extract information for street addresses, sometimes we receive house
number duplicated example:

123 123 Main Street

Does anyone know of a way to delete the first number so we just have the
correct address of 123 Main Street?

Thanks

 

[Don Guillett]

Sub dupstreetnumbersinstring()
lr = Cells(Rows.Count, "a").End(xlUp).Row
For Each c In Range("a1:a" & lr)
x = InStr(c, " ")
If Left(c, x) = Mid(c, x + 1, x) Then
MsgBox c.Row
c.Value = Mid(c, x + 1, 999999999)
End If
Next c
End Sub

 

[Gary''s Student]

With the addresses in column A, try this small macro:

Sub FixAddress()
Set rr = Intersect(ActiveSheet.UsedRange, Range("A:A"))
For Each r In rr
parts = Split(r.Value, " ")
If IsNumeric(parts(0)) And parts(0) = parts(1) Then
parts(0) = ""
r.Value = Trim(Join(parts, " "))
End If
Next
End Sub

Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To use the macro from the normal Excel window:

1. ALT-F8
2. Select the macro
3. Touch Run



To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Ron Rosenfeld]

Hello,

we extract information for street addresses, sometimes we receive house
number duplicated example:

123 123 Main Street

Does anyone know of a way to delete the first number so we just have the
correct address of 123 Main Street?

Thanks

Here is a macro that will remove the first word in a string IF it is repeated
at the second word.

I chose to not restrict the test to just numbers, thinking that sometimes
addresses include both numbers and letters: e.g. 123A 123A Fourth Avenue

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first select the range you wish to process. Then
<alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

======================================
Option Explicit
Sub DeDupAdr()
Dim c As Range
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "(\b\w+\b)\s+(?=\1)"
For Each c In Selection
c.Value = re.Replace(c.Value, "")
Next c
End Sub
======================================
--ron