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 |
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 |
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 |
All times are GMT +1. The time now is 02:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com