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

[SOLTEC]

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.

 

[alansidman]

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]

Funeral Homes in Tracy

Almlie Funeral Home

444 Craig Avenue

Tracy

Minnesota 56175

Phone: (507) 629-4510

 

[SOLTEC]

Funeral Homes in Tracy
Almlie Funeral Home	Almlie Funeral Home	444 Craig Avenue	Tracy	Minnesota 56175	Phone: (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]

Funeral Homes in Tracy
Almlie Funeral Home	Almlie Funeral Home	444 Craig Avenue	Tracy	Minnesota 56175	Phone: (507) 629-4510
444 Craig Avenue	Tracy Area Funeral Home	372 3rd Street	Tracy	Minnesota 56175	Phone: (507) 629-4680
Tracy	Kramer Funeral Home	71 2nd Ave Se	Trimont	Minnesota 56176	Phone: (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]

Using Power Query/Get and Transform here is the Mcode

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
	A	B	C	D	E
1	Column1	Street	City	State	Phone
2	Almlie Funeral Home	444 Craig Avenue	Tracy	Minnesota 56175	Phone: (507) 629-4510
3	Tracy Area Funeral Home	372 3rd Street	Tracy	Minnesota 56175	Phone: (507) 629-4680
4	Kramer Funeral Home	71 2nd Ave Se	Trimont	Minnesota 56176	Phone: (507) 639-2281
5	Olson-Zaharia Funeral Home	106 N 2nd Ave E	Truman	Minnesota 56088	Phone: (507) 776-4375
6	Anderson Funeral Home	215 West Main Street	Twin Valley	Minnesota 56584	Phone: (218) 584-5166
Sheet2

 

[Trebor76]

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

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]

I am attempting to locate Power Query in Excel 2019 unsuccessfully, can you assist me with this, please.
Thank you in advance.

 

[alansidman]

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.

 

[TotallyConfused]

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)