Remove rows that contain apartment addresses

kellman

New Member
Joined
Aug 25, 2012
Messages
32
Office Version
  1. 365
Platform
  1. Windows
I would like to remove rows that contain any apartment style addressing, such as anything with a street number and unit number.

Example apartment address:
2 - 234 Smith Street

Example house address:
167 Jones Avenue



The data I'm importing into my excel sheet contains both apartment and house style addressing.

I need a way to display only house addresses, and not any apartments.

One thing in common about apartment addresses is they uniquely have a hyphen or dash - character.

Is there a way to tell excel to remove rows that contain addresses that have one or more hyphens?

I really appreciate your help!

Kelly
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Suggest you upload with XL2BB a representative sample of your data 8-10 records and then a mocked up solution of same. Do not post a picture as we can not manipulate data in a picture.
 
Upvote 0
GLB for Dec 9.xlsx
AB
1Original imported data
2
3Street NumberCity
46 - 2096 DE VRIES AVEWINNIPEG
564 RILLWILLOW PLACEWINNIPEG
655 EAU-CLAIRE DRIVEWINNIPEG
723 - 150 GEORGE BARONE BAYWINNIPEG
822 - 35 BASEL AVEWINNIPEG
922 DEDRICK BAYWINNIPEG
1050 - 900 VAUGHAN AVESELKIRK
11
12
13Final Look needed example
14
15Street NumberCity
1664 RILLWILLOW PLACEWINNIPEG
1755 EAU-CLAIRE DRIVEWINNIPEG
1822 DEDRICK BAYWINNIPEG
Sheet1
 
Upvote 0
Suggest you upload with XL2BB a representative sample of your data 8-10 records and then a mocked up solution of same. Do not post a picture as we can not manipulate data in a picture.
Good idea. Take a look at my example post xl2bb
 
Upvote 0
I'm using Office 365 so the newest version.
Please add that to your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Is there a way to tell excel to remove rows that contain addresses that have one or more hyphens?
:unsure:
Not sure that would be a good idea. What about your row 6 that contains a hyphen but is included in your desired results?
Do all the 'unit' ones have spaces characters each side of the dash like you samples?
Would something like this be any use?

23 01 01.xlsm
AB
3Street NumberCity
46 - 2096 DE VRIES AVEWINNIPEG
564 RILLWILLOW PLACEWINNIPEG
655 EAU-CLAIRE DRIVEWINNIPEG
723 - 150 GEORGE BARONE BAYWINNIPEG
822 - 35 BASEL AVEWINNIPEG
922 DEDRICK BAYWINNIPEG
1050 - 900 VAUGHAN AVESELKIRK
11
12
13Street NumberCity
1464 RILLWILLOW PLACEWINNIPEG
1555 EAU-CLAIRE DRIVEWINNIPEG
1622 DEDRICK BAYWINNIPEG
Addresses
Cell Formulas
RangeFormula
A14:B16A14=FILTER(A4:B10,ISERROR(FIND(" - ",A4:A10)))
Dynamic array formulas.
 
Upvote 0
Not sure that would be a good idea. What about your row 6 that contains a hyphen but is included in your desired results?
Do all the 'unit' ones have spaces characters each side of the dash like you samples?
Would something like this be any use?

I just tried your solution, and it works. Thank you!

That is a very good observation, I never noticed the additional hyphen in my example for Row 6.
Yes all of the imported data have addresses listed with one space before and one space after the hyphen.

To make things more complicated, I just noticed some of the Cells contain other location information seperated by another hyphen.
I'm wondering if your Filter idea, instead of looking for (" - ") the hyphen within the cell, perhaps it could look for (number - number) and exclude those rows?


FTTH GLB DEC 9TH.csv
A
1Address
2402 - 775 STERLING LYON PKWY - TUX
3140 KAIRISTINE LANE - WPG
4133 LINDSAY ST
526 KEYSTONE CRES - OKLD
669 MICHAUD CRES - STVTL
72096 DE VRIES AV
8103 - 1032 WILKES AV - TUX
9128 CALLUM CRES - NKLD
1015 TUNIS BAY - FTGRY
119 RAPHAEL ST - FTGRY
12202 - 707 STERLING LYON PKWY - TUX
13412 - 2475 WAVERLEY ST - FTGRY
14408 YALE AV E - TRANS
15228 - 55 DAYLAN MARSHALL GATE - WPG
16404 - 707 STERLING LYON PKWY - TUX
17322 CHURCH AV - FLR 2 - WPG
18325 HARVARD AV W - TRANS
1915 HADLEY PL - FTGRY
2093 EGESZ ST - WPG
2137 - 59 UNIVERSITY CRES - FTGRY
2241 ROSLYN CRES - WPG
23428 REGENT AV E - TRANS
2412 - 55 UNIVERSITY CRES - FTGRY
2510 ALDERWOOD RD - STBON
261517 - 1750 PEMBINA HWY - FTGRY
2727 STREWCHUK BAY - OKLD
28279 HAZELWOOD AV - STVTL
FTTH GLB DEC 9TH
 
Upvote 0
Try on a copy of your original first.
Code:
Sub Maybe()
Dim i As Long
    For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        If InStr(Cells(i, 1), "-") <> 0 Then
            If Asc(Mid(Cells(i, 1), InStr(Cells(i, 1), "-") - 2, 1)) > 47 And Asc(Mid(Cells(i, 1).Value, InStr(Cells(i, 1), "-") + 2, 1)) < 58 Then Cells(i, 1).Delete Shift:=xlUp
        End If
    Next i
End Sub
 
Upvote 0
Here is another macro that you can try...
VBA Code:
Sub Maybe2()
  Dim R As Long
  For R = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
    If Cells(R, "A").Value Like "*# - #* *" Then Rows(R).Delete
  Next
End Sub
 
Upvote 0
Solution

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top