# Remove rows that contain apartment addresses



## kellman (Dec 31, 2022)

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 (Dec 31, 2022)

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 (Dec 31, 2022)

GLB for Dec 9.xlsxAB1Original imported data23Street NumberCity46 - 2096 DE VRIES AVEWINNIPEG564 RILLWILLOW PLACEWINNIPEG655 EAU-CLAIRE DRIVEWINNIPEG723 - 150 GEORGE BARONE BAYWINNIPEG822 - 35 BASEL AVEWINNIPEG922 DEDRICK BAYWINNIPEG1050 - 900 VAUGHAN AVESELKIRK111213Final Look needed example1415Street NumberCity1664 RILLWILLOW PLACEWINNIPEG1755 EAU-CLAIRE DRIVEWINNIPEG1822 DEDRICK BAYWINNIPEGSheet1


----------



## kellman (Dec 31, 2022)

alansidman said:


> 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 (Dec 31, 2022)

What version of Excel are you using?


----------



## kellman (Dec 31, 2022)

Rick Rothstein said:


> What version of Excel are you using?


I'm using Office 365 so the newest version.


----------



## Peter_SSs (Jan 1, 2023)

kellman said:


> 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’)



kellman said:


> 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.xlsmAB3Street NumberCity46 - 2096 DE VRIES AVEWINNIPEG564 RILLWILLOW PLACEWINNIPEG655 EAU-CLAIRE DRIVEWINNIPEG723 - 150 GEORGE BARONE BAYWINNIPEG822 - 35 BASEL AVEWINNIPEG922 DEDRICK BAYWINNIPEG1050 - 900 VAUGHAN AVESELKIRK111213Street NumberCity1464 RILLWILLOW PLACEWINNIPEG1555 EAU-CLAIRE DRIVEWINNIPEG1622 DEDRICK BAYWINNIPEGAddressesCell FormulasRangeFormulaA14:B16A14=FILTER(A4:B10,ISERROR(FIND(" - ",A4:A10)))Dynamic array formulas.


----------



## kellman (Jan 1, 2023)

Peter_SSs said:


> 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.csvA1Address2402 - 775 STERLING LYON PKWY - TUX3140 KAIRISTINE LANE - WPG4133 LINDSAY ST526 KEYSTONE CRES - OKLD669 MICHAUD CRES - STVTL72096 DE VRIES AV8103 - 1032 WILKES AV - TUX9128 CALLUM CRES - NKLD1015 TUNIS BAY - FTGRY119 RAPHAEL ST - FTGRY12202 - 707 STERLING LYON PKWY - TUX13412 - 2475 WAVERLEY ST - FTGRY14408 YALE AV E - TRANS15228 - 55 DAYLAN MARSHALL GATE - WPG16404 - 707 STERLING LYON PKWY - TUX17322 CHURCH AV - FLR 2 - WPG18325 HARVARD AV W - TRANS1915 HADLEY PL - FTGRY2093 EGESZ ST - WPG2137 - 59 UNIVERSITY CRES - FTGRY2241 ROSLYN CRES - WPG23428 REGENT AV E - TRANS2412 - 55 UNIVERSITY CRES - FTGRY2510 ALDERWOOD RD - STBON261517 - 1750 PEMBINA HWY - FTGRY2727 STREWCHUK BAY - OKLD28279 HAZELWOOD AV - STVTLFTTH GLB DEC 9TH


----------



## jolivanes (Jan 1, 2023)

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 (Jan 1, 2023)

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
```


----------



## kellman (Dec 31, 2022)

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


----------



## kellman (Jan 1, 2023)

jolivanes said:


> Try on a copy of your original first.
> 
> ```
> Sub Maybe()
> ...



Oh wow, that works nicely.

Thank you for taking the time to help me!

Happy New Year


----------



## kellman (Jan 1, 2023)

Rick Rothstein said:


> Here is another macro that you can try...
> 
> ```
> Sub Maybe2()
> ...



Brilliant, this also works.

I don't have any VB experience so for me it's hard to tell which of the two solutions are better "maybe" or "maybe2".

Thank you so very much!!


----------



## jolivanes (Jan 1, 2023)

If you have a large range of data and you want to stay away from looping.

```
Sub Another_Possibility()
Dim cons, i As Long, rws As Range
cons = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value
    For i = LBound(cons) To UBound(cons)
        If InStr(cons(i, 1), "-") <> 0 Then
            If Asc(Mid(cons(i, 1), InStr(cons(i, 1), "-") - 2, 1)) > 47 And Asc(Mid(cons(i, 1), InStr(cons(i, 1), "-") + 2, 1)) < 58 Then
                If Not rws Is Nothing Then
                    Set rws = Union(rws, Cells(i + 1, 1).EntireRow)
                        Else
                    Set rws = Cells(i + 1, 1).EntireRow
                End If
            End If
        End If
    Next i
rws.Select    '<---- Change to delete if happy.
End Sub
```
To make it more up to date, replace part of above with Rick's (better) suggestion.


----------



## kellman (Jan 1, 2023)

Thank you, I will definitely give your suggestion a try.

The raw CSV data that I copy & paste into Excel each day has 112 Columns and anywhere from 20 to 70 Rows

I just learned how to use the Macro Recorder to trim down the columns down to about 5 columns (the ones with the data I need).  What a fantastic tool and time saver!

The last part I need to figure out is text formatting, or rather how to automate the text formatting.

The Macro I recorded on the spreadsheet I used only had about 20 rows.  But then what I noticed is, when I ran the same Macro on a sheet with 70 Rows, it stopped formatting my text at row 20.

I'm thinking I'll need something more specific for sheets with varying Rows of data

Perhaps VB Script is the solution again.

I wonder if I should post an example of my text formatting here, or if I should create a new topic/thread?

Regards

Nothing to eloborate, Column Heading in Bold and then each Row I'm using the format "bottom border" button.

GLB for Dec 8.xlsxA1Address2215 GREENWAY CRES W379 BERRY HILL RD472 GREENSBORO BAY538 OAKLEAF DR6920 CHANCELLOR DR7131 WINTERHAVEN DR - STVTL815 OAKRIDGE BAY - STVTL9375 CASTLEBURY MEADOWS DR - OKLD10427 - 20 INNSBRUCK WAY - WPG1138 FETTERLY WAY12241 WALES AV - STVTL1351 CURRY DR - HDLY14115 BRIARLYNN RD - FTGRY15190 LEAHCREST CRES16119 RYERSON AV17159 CREEKSIDE RD - FTGRY1878 RUTGERS BAY - FTGRY1977 GALBRAITH CRES - ASNBOIA2014 GEORGE BARONE BAY - NKLD2151 CAUSEWAY BAY - STBON223403 GRANT AV - CHLSWD2370 MARSEILLES CL - WPG2465 COLCHESTER BAY - TUX25115 BAYLOR AV - FTGRY26147 GEMINI AV - NKLD27737 SHEPPARD ST - WPG28141 BAYLOR AV - FTGRY2923 TIMMINS PL - WPG3015 BAIRD PL - NKLD31158 BRIXTON BAY - STVTL3279 TARASKA BAY - WPG337051 PR 247 E3423 SCHAUBROECK RD3520 VERONA DR363373 PEMBINA HWY3746 SERENITY COVE38172 BERRY HILL RD - FTGRY39344 NOVAVISTA DR - STVTL40713 DE LA SEIGNEURIE BLVD - STBON417 EDITH BAY - NKLD42293 APPLEFORD GATE - FTGRY4360 VAN HORNE RDSheet1


----------



## jolivanes (Jan 1, 2023)

Since it is a different problem, I would think that a new thread would be better. 
If you need to refer to this thread, just put a hyperlink with the address of this thread in your post.


----------



## Peter_SSs (Jan 1, 2023)

For the original question, it is looking like a macro may be best for you but if you wanted to pursue a formula approach, you could try this version.
Formula still only required in the very top cell of the results area.

23 01 01.xlsmABC1Address2402 - 775 STERLING LYON PKWY - TUX140 KAIRISTINE LANE - WPG3140 KAIRISTINE LANE - WPG133 LINDSAY ST4133 LINDSAY ST26 KEYSTONE CRES - OKLD526 KEYSTONE CRES - OKLD69 MICHAUD CRES - STVTL669 MICHAUD CRES - STVTL2096 DE VRIES AV72096 DE VRIES AV128 CALLUM CRES - NKLD8103 - 1032 WILKES AV - TUX15 TUNIS BAY - FTGRY9128 CALLUM CRES - NKLD9 RAPHAEL ST - FTGRY1015 TUNIS BAY - FTGRY408 YALE AV E - TRANS119 RAPHAEL ST - FTGRY322 CHURCH AV - FLR 2 - WPG12202 - 707 STERLING LYON PKWY - TUX325 HARVARD AV W - TRANS13412 - 2475 WAVERLEY ST - FTGRY15 HADLEY PL - FTGRY14408 YALE AV E - TRANS93 EGESZ ST - WPG15228 - 55 DAYLAN MARSHALL GATE - WPG41 ROSLYN CRES - WPG16404 - 707 STERLING LYON PKWY - TUX428 REGENT AV E - TRANS17322 CHURCH AV - FLR 2 - WPG10 ALDERWOOD RD - STBON18325 HARVARD AV W - TRANS27 STREWCHUK BAY - OKLD1915 HADLEY PL - FTGRY279 HAZELWOOD AV - STVTL2093 EGESZ ST - WPG2137 - 59 UNIVERSITY CRES - FTGRY2241 ROSLYN CRES - WPG23428 REGENT AV E - TRANS2412 - 55 UNIVERSITY CRES - FTGRY2510 ALDERWOOD RD - STBON261517 - 1750 PEMBINA HWY - FTGRY2727 STREWCHUK BAY - OKLD28279 HAZELWOOD AV - STVTLAddresses (2)Cell FormulasRangeFormulaC2:C19C2=FILTER(A2:A28,ISERROR(-SUBSTITUTE(MID(A2:A28,FIND(" - ",A2:A28)-1,5)," - ","")))Dynamic array formulas.


----------



## Peter_SSs (Jan 1, 2023)

kellman said:


> Brilliant, this also works.


The marked solution has been changed accordingly.
In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.


----------



## kellman (Jan 2, 2023)

One last question still relating to my original question.
When I use the VB Code you provided on a sheet with more than 1 Column, it doesn't work.

I will provide a more accurate example of what my final project sheet will look like.

I'm assuming the Range will need to be changed in order for the VB script to work correctly.

There will be 5 fixed Columns but the number of Rows will need to be variable somehow.

I would appreciate if you could take one last look at my scenario.


FTTH GLB DEC 8TH.csvABCDE1Start TimeAddressCityJob idSource Order ID23456789101112FTTH GLB DEC 8TH


----------



## jolivanes (Jan 2, 2023)

You forgot to specify which macro.
Post #9

```
Sub Maybe_Column_B()
Dim i As Long
    For i = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
        If InStr(Cells(i, 2), "-") <> 0 Then
            If Asc(Mid(Cells(i, 2), InStr(Cells(i, 2), "-") - 2, 1)) > 47 And Asc(Mid(Cells(i, 2).Value, InStr(Cells(i, 2), "-") + 2, 1)) < 58 Then Cells(i, 2).Offset(, -1).Resize(, 5).Delete Shift:=xlUp
        End If
    Next i
End Sub
```
Post #11

```
Sub Another_Possibility_Column_B()
Dim cons, i As Long, rws As Range
cons = Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row).Value
    For i = LBound(cons) To UBound(cons)
        If InStr(cons(i, 1), "-") <> 0 Then
            If Asc(Mid(cons(i, 1), InStr(cons(i, 1), "-") - 2, 1)) > 47 And Asc(Mid(cons(i, 1), InStr(cons(i, 1), "-") + 2, 1)) < 58 Then
                If Not rws Is Nothing Then
                    Set rws = Union(rws, Cells(i + 1, 1).EntireRow)
                        Else
                    Set rws = Cells(i + 1, 1).EntireRow
                End If
            End If
        End If
    Next i
rws.Select    '<---- Change to delete if happy.
End Sub
```

For Rick's code, if he permits me

```
Sub Maybe2()
  Dim R As Long
  For R = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
    If Cells(R, "B").Value Like "*# - #* *" Then Rows(R).Delete
  Next
End Sub
```


----------



## kellman (Jan 4, 2023)

Lets go with Post # 9 Code

Sub Maybe_Column_B()
Dim i As Long
    For i = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
        If InStr(Cells(i, 2), "-") <> 0 Then
            If Asc(Mid(Cells(i, 2), InStr(Cells(i, 2), "-") - 2, 1)) > 47 And Asc(Mid(Cells(i, 2).Value, InStr(Cells(i, 2), "-") + 2, 1)) < 58 Then Cells(i, 2).Offset(, -1).Resize(, 5).Delete Shift:=xlUp
        End If
    Next i
End Sub


```
Sub Maybe_Column_B()
Dim i As Long
    For i = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
        If InStr(Cells(i, 2), "-") <> 0 Then
            If Asc(Mid(Cells(i, 2), InStr(Cells(i, 2), "-") - 2, 1)) > 47 And Asc(Mid(Cells(i, 2).Value, InStr(Cells(i, 2), "-") + 2, 1)) < 58 Then Cells(i, 2).Offset(, -1).Resize(, 5).Delete Shift:=xlUp
        End If
    Next i
End Sub
```


----------



## kellman (Dec 31, 2022)

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


----------



## Peter_SSs (Jan 4, 2023)

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have repeated your code in the previous post and added the tags to the copy so that you can see the difference. 😊


----------



## kellman (Jan 4, 2023)

jolivanes said:


> You forgot to specify which macro.
> Post #9
> 
> ```
> ...



Lets go with Post # 9 Code

Thank you.


----------



## Rick Rothstein (Jan 4, 2023)

kellman said:


> Lets go with Post # 9 Code
> 
> Sub Maybe_Column_B()
> Dim i As Long
> ...


If you are going with Post #9, then why is my post marked as the Answer? You should change that and mark Post #9 as the Answer.


----------



## Peter_SSs (Jan 4, 2023)

Rick Rothstein said:


> why is my post marked as the Answer?


Because I marked it as the solution. 
Reason: In post #12 the OP had quoted your #10 post, commented "Brilliant, this also works." & marked *that post *(#12) as the solution - which clearly it isn't.
Hence my quote & comment at post #17, ay which point I marked your post. The OP is welcome to further change the marked solution should they so desire.


----------



## kellman (Jan 5, 2023)

jolivanes said:


> You forgot to specify which macro.
> Post #9
> 
> ```
> ...



@jolivanes 

Regarding my Question on Post #18

You asked me which of the 3 suggested codes posted would I like to use.
I'll go with the code from post # 9 (Below)
What is your suggestion to modify this code when the number of columns are fixed to 5, BUT the number of Rows will vary each day. 
Some days i have 20 rows and other days I have 70 rows of data.

--------------------------------------------
VBA Code:
Sub Maybe_Column_B()
Dim i As Long
For i = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
If InStr(Cells(i, 2), "-") <> 0 Then
If Asc(Mid(Cells(i, 2), InStr(Cells(i, 2), "-") - 2, 1)) > 47 And Asc(Mid(Cells(i, 2).Value, InStr(Cells(i, 2), "-") + 2, 1)) < 58 Then Cells(i, 2).Offset(, -1).Resize(, 5).Delete Shift:=xlUp
End If
 Next i
End Sub
---------------------------------------------


----------



## jolivanes (Jan 5, 2023)

What happened to the 5 cells in Columns A to E when you tried it with a few rows making sure you had some data in column B that needed deleting and later tried it with lots of rows, again with data in column B that needed deleting?

Please use code tags and don't quote if not needed.


----------



## kellman (Jan 5, 2023)

jolivanes said:


> What happened to the 5 cells in Columns A to E when you tried it with a few rows making sure you had some data in column B that needed deleting and later tried it with lots of rows, again with data in column B that needed deleting?
> 
> Please use code tags and don't quote if not needed.


It works!
Sorry for my misunderstanding.
I thought you were asking me to pick one of the three suggested VB codes, and then you'd make the adjustment.

Everything works perfectly now.

Thank you!


----------



## jolivanes (Jan 5, 2023)

Thanks for the update and good luck.


----------

