Find all cells in column that row contains specific string and delete those rows.

SOLTEC

Board Regular
Joined
Feb 11, 2015
Messages
180
I have a huge spreadsheet that has names, addresses, city, state and zip code, with phone numbers that will be five rows in one column. What I'm trying to do is remove the rows that contain a partial string of, "Funeral Homes in" and a wildcard for the additional string characters. I don't know if this is permitted in the same post but then would like to take the names, addresses... that are five rows and separate them into their own columns with another formula. There is a single empty row between the other five rows currently, or will be when I'm able to delete the "Funeral Homes in" rows. Thank you in advance for any and all assistance.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,148
Office Version
  1. 365
Platform
  1. Windows
How about sharing a sample worksheet using XL2BB. Perhaps 5 to 8 records. Also, show what your solution should look like for the records provided.
 

SOLTEC

Board Regular
Joined
Feb 11, 2015
Messages
180
Funeral Homes in Tracy
Almlie Funeral Home
444 Craig Avenue
Tracy
Minnesota 56175
Phone: (507) 629-4510
 

SOLTEC

Board Regular
Joined
Feb 11, 2015
Messages
180
Funeral Homes in Tracy
Almlie Funeral HomeAlmlie Funeral Home444 Craig AvenueTracyMinnesota 56175Phone: (507) 629-4510
444 Craig Avenue
Tracy
Minnesota 56175
Phone: (507) 629-4510
Tracy Area Funeral Home
372 3rd Street
Tracy
Minnesota 56175
Phone: (507) 629-4680
Funeral Homes in Trimont
Kramer Funeral Home
71 2nd Ave Se
Trimont
Minnesota 56176
Phone: (507) 639-2281
Funeral Homes in Truman
Olson-Zaharia Funeral Home
106 N 2nd Ave E
Truman
Minnesota 56088
Phone: (507) 776-4375
Funeral Homes in Twin Valley
Anderson Funeral Home
215 West Main Street
Twin Valley
Minnesota 56584
Phone: (218) 584-5166
 

SOLTEC

Board Regular
Joined
Feb 11, 2015
Messages
180

ADVERTISEMENT

Funeral Homes in Tracy
Almlie Funeral HomeAlmlie Funeral Home444 Craig AvenueTracyMinnesota 56175Phone: (507) 629-4510
444 Craig AvenueTracy Area Funeral Home372 3rd StreetTracyMinnesota 56175Phone: (507) 629-4680
TracyKramer Funeral Home71 2nd Ave SeTrimontMinnesota 56176Phone: (507) 639-2281
Minnesota 56175
Phone: (507) 629-4510
Tracy Area Funeral Home
372 3rd Street
Tracy
Minnesota 56175
Phone: (507) 629-4680
Funeral Homes in Trimont
Kramer Funeral Home
71 2nd Ave Se
Trimont
Minnesota 56176
Phone: (507) 639-2281
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,148
Office Version
  1. 365
Platform
  1. Windows
Using Power Query/Get and Transform here is the Mcode

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> null)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each if Text.StartsWith([Column1], "Funeral Homes") then null else [Column1]),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Custom] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Custom"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 5), type number),
    #"Added Custom1" = Table.AddColumn(#"Inserted Modulo", "Street", each if [Modulo]=1 then [Column1] else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "City", each if [Modulo]= 2 then [Column1] else null),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "State", each if [Modulo]= 3 then [Column1] else null),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Phone", each if [Modulo]=4 then [Column1] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom4",{"Street", "City", "State", "Phone"}),
    #"Filtered Rows2" = Table.SelectRows(#"Filled Up", each ([Modulo] = 0)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows2",{"Index", "Modulo"})
in
    #"Removed Columns1"

Funeral.xlsm
ABCDE
1Column1StreetCityStatePhone
2Almlie Funeral Home444 Craig AvenueTracyMinnesota 56175Phone: (507) 629-4510
3Tracy Area Funeral Home372 3rd StreetTracyMinnesota 56175Phone: (507) 629-4680
4Kramer Funeral Home71 2nd Ave SeTrimontMinnesota 56176Phone: (507) 639-2281
5Olson-Zaharia Funeral Home106 N 2nd Ave ETrumanMinnesota 56088Phone: (507) 776-4375
6Anderson Funeral Home215 West Main StreetTwin ValleyMinnesota 56584Phone: (218) 584-5166
Sheet2
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,731

ADVERTISEMENT

Hi SOLTEC,

Try this (just make sure to set the three variables I've marked as "Change to suit" towards to the top of the procedure):

VBA Code:
Option Explicit
Sub Macro1()

    Dim ws As Worksheet
    Dim strDataCol As String
    Dim lngStartRow As Long, lngMyRow As Long, lngLastRow As Long
    Dim lngPasteCol As Long
    
    Application.ScreenUpdating = False
    
    Set ws = ThisWorkbook.Sheets("Sheet5") 'Sheet name containing data. Change to suit.
    strDataCol = "A" 'Column containing data. Change to suit.
    lngStartRow = 1 'Starting Row number for the data in column 'strDataCol'. Change to suit.
    
    With ws
        'Clear any row(s) in column 'strDataCol' that starts with 'Funeral Homes in'
        lngLastRow = .Cells(Rows.Count, strDataCol).End(xlUp).Row
        For lngMyRow = lngStartRow To lngLastRow
            If StrConv(Left(.Range(strDataCol & lngMyRow), 16), vbUpperCase) = "FUNERAL HOMES IN" Then
                .Range(strDataCol & lngMyRow).ClearContents
            End If
        Next lngMyRow
        'Transpose each block of five rows
        On Error Resume Next
            lngPasteCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            lngPasteCol lngPasteCol + 1
        On Error GoTo 0
        For lngMyRow = lngStartRow To lngLastRow
            If Len(.Range(strDataCol & lngMyRow)) > 0 Then
                Range(.Cells(lngMyRow, strDataCol), .Cells(lngMyRow + 5, strDataCol)).Copy
                .Cells(lngMyRow, lngPasteCol).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
                Application.CutCopyMode = False
                lngMyRow = lngMyRow + 5 'Set the 'lngMyRow' variable to the last row in the block of data just transposed
            End If
        Next lngMyRow
    End With
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert
 

SOLTEC

Board Regular
Joined
Feb 11, 2015
Messages
180
I am attempting to locate Power Query in Excel 2019 unsuccessfully, can you assist me with this, please.
Thank you in advance.
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,148
Office Version
  1. 365
Platform
  1. Windows
In Excel 2019, Power Query has been renamed as Get and Transform. It is located on the Data Tab of the Ribbon. Check out the link in my signature for more information. Capture2.JPG
Capture2.JPG
 

TotallyConfused

Board Regular
Joined
May 4, 2017
Messages
246
Office Version
  1. 365
Platform
  1. Windows
Hello Alansidman

THANK YOU for your explanation above about how to locate Power Query (or its new name) from within Excel. I've seen answers given in Power Query posted here before, but never knew what it was. I thought maybe it was some Add-In we had to download.

TotallyConfused (I still am but now a speck wiser) :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,266
Messages
5,623,714
Members
415,983
Latest member
MusicMan

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
Top