Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting duplicate house numbers
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting duplicate house numbers
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Sarah" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting duplicate house numbers
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 -- Gary''s Student - gsnu201001 "Sarah" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I look for duplicate house addresses in a column in Excel? | Excel Worksheet Functions | |||
Deleting duplicate row | Excel Discussion (Misc queries) | |||
Deleting Duplicate Numbers In A Column: How ? | New Users to Excel | |||
Removing house numbers from addresses | Excel Worksheet Functions | |||
Deleting BOTH duplicate entries | Excel Discussion (Misc queries) |