Need to remove non-matching text from a delimited string

I have an Excel database query to get all the RBAC user roles that are assigned to each user, and the database returns a string delimited by & (ampersands) between each user role, e.g.:

Please Login or Register  to view this content.

My query filters records that only have a matching string, let's say it's "Reports". However it still returns the full list of user roles for a matching user, and in this case some users have >10 roles assigned and it makes the table look really messy and not suitable for printing.

I could manually clean up each row, but there are quite a lot of them, and since this is going to be run regularly I'm wondering if there may be a good Excel formula or VBS method to split delimited sections of a string and only keep ones that match a string criteria.

I'm aware of "Text to Columns" and its ability to make use of delimiters, but it just spat out a ton of columns and made things worse. I've done several searches about cleaning up delimited strings in Excel but couldn't find any results that were similar to my situation: need to split a delimited string and do something RegEx-esque to only keep parts that match a pattern.

Ideally I'd like to keep the cleaned up results in a single cell, so the above example "&Admin&Supervisor&ViewReports&WriteReports&" would look like:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

or similar, in a single cell. Not too picky about formatting really, just need the non-relevant parts of the string gone.


I was given this formula on another website:

Please Login or Register  to view this content.

With {3,5} it gives me the 2nd and 4th substring, so "Supervisor WriteReports". However I can't figure out how to make it work off of a MATCH instead of numerical indexes, and the numerical indexes just aren't helpful since an account could have 3 user roles or over 15. I'd need to match it to "*Reports*" but I can't figure out the syntax of this formula or what goes where to work in the MATCH function.

Any help or ideas would be appreciated, thanks.

Hi, and welcome to the forum.

Does this UDF help.
With the record in A1 and the comparison text e.g. 'Reports' in B1

use

Formula:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Wow, thank you so much! That worked like a charm!

For readability I'm just going to need need to remove the first "&" character from the string but I should be able to figure that out pretty easily. Thanks again!

EDIT: For the record, I just removed cruft from the string after the fact by doing this at the end:

Please Login or Register  to view this content.

My pleasure and thanks for the rep.