SOLVED

Removing rows that contain a number of specific characters anywhere in a cell

Occasional Contributor


How would you remove all rows in a sheet that contain anymore then 5 / in a cell?

We're creating a Visio chart of a SharePoint directory that has many folders, however we only need to see the first few layers. Otherwise it tries to chart nearly 4000 folders

e.g
The below layer is fine
teams/accounts/Shared Documents/affiliates/processes/subprocesses  

But to remove any row that has anything more than that
teams/accounts/Shared Documents/affiliates/processes/subprocesses/thing1/thing2/thing3/....  

If a row has a cell that has a directory than has more the 5 / it needs to go.


Is there anyway to achieve this automatically?

Thanks

2 Replies
best response confirmed by silaman (Occasional Contributor)
Solution

@silaman I would use Power Query for such a task. You'll find a working example in the attached workbook.

Wow, thanks a lot

Looks like I will have to get better at Power Query