Remove rows that contain apartment addresses

[kellman]

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

 

[alansidman]

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.

 

[kellman]

GLB for Dec 9.xlsx
	A	B
1	Original imported data
2
3	Street Number	City
4	6 - 2096 DE VRIES AVE	WINNIPEG
5	64 RILLWILLOW PLACE	WINNIPEG
6	55 EAU-CLAIRE DRIVE	WINNIPEG
7	23 - 150 GEORGE BARONE BAY	WINNIPEG
8	22 - 35 BASEL AVE	WINNIPEG
9	22 DEDRICK BAY	WINNIPEG
10	50 - 900 VAUGHAN AVE	SELKIRK
11
12
13	Final Look needed example
14
15	Street Number	City
16	64 RILLWILLOW PLACE	WINNIPEG
17	55 EAU-CLAIRE DRIVE	WINNIPEG
18	22 DEDRICK BAY	WINNIPEG
Sheet1

 

[kellman]

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

 

[Rick Rothstein]

What version of Excel are you using?

 

[kellman]

What version of Excel are you using?

I'm using Office 365 so the newest version.

 

[Peter_SSs]

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?

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
	A	B
3	Street Number	City
4	6 - 2096 DE VRIES AVE	WINNIPEG
5	64 RILLWILLOW PLACE	WINNIPEG
6	55 EAU-CLAIRE DRIVE	WINNIPEG
7	23 - 150 GEORGE BARONE BAY	WINNIPEG
8	22 - 35 BASEL AVE	WINNIPEG
9	22 DEDRICK BAY	WINNIPEG
10	50 - 900 VAUGHAN AVE	SELKIRK
11
12
13	Street Number	City
14	64 RILLWILLOW PLACE	WINNIPEG
15	55 EAU-CLAIRE DRIVE	WINNIPEG
16	22 DEDRICK BAY	WINNIPEG
Addresses

Cell Formulas
Range	Formula
A14:B16	A14	=FILTER(A4:B10,ISERROR(FIND(" - ",A4:A10)))
Dynamic array formulas.

 

[kellman]

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
1	Address
2	402 - 775 STERLING LYON PKWY - TUX
3	140 KAIRISTINE LANE - WPG
4	133 LINDSAY ST
5	26 KEYSTONE CRES - OKLD
6	69 MICHAUD CRES - STVTL
7	2096 DE VRIES AV
8	103 - 1032 WILKES AV - TUX
9	128 CALLUM CRES - NKLD
10	15 TUNIS BAY - FTGRY
11	9 RAPHAEL ST - FTGRY
12	202 - 707 STERLING LYON PKWY - TUX
13	412 - 2475 WAVERLEY ST - FTGRY
14	408 YALE AV E - TRANS
15	228 - 55 DAYLAN MARSHALL GATE - WPG
16	404 - 707 STERLING LYON PKWY - TUX
17	322 CHURCH AV - FLR 2 - WPG
18	325 HARVARD AV W - TRANS
19	15 HADLEY PL - FTGRY
20	93 EGESZ ST - WPG
21	37 - 59 UNIVERSITY CRES - FTGRY
22	41 ROSLYN CRES - WPG
23	428 REGENT AV E - TRANS
24	12 - 55 UNIVERSITY CRES - FTGRY
25	10 ALDERWOOD RD - STBON
26	1517 - 1750 PEMBINA HWY - FTGRY
27	27 STREWCHUK BAY - OKLD
28	279 HAZELWOOD AV - STVTL
FTTH GLB DEC 9TH

 

[jolivanes]

Try on a copy of your original first.

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

 

[Rick Rothstein]

Here is another macro that you can try...

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