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]

If you are truly interested in learning PQ, then pick up the book "M is for (Data) Monkey" by Ken Puls and Miguel Escobar. Available on Amazon and the home page on MrE in his bookstore.

 

[SOLTEC]

When I open the Power Query editor there are no menu items enabled. Where am I supposed to run the code for the query?? Thank you in advance as I have been doing this manually one row at a time.

 

[alansidman]

Open Power Query/Get and Transform. Click on New Query.

Open blank query in the editor, launch Advanced Editor and paste in the following code.