This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I have a column with that contains initials of authors. What I need to do is filter out some authors, leaving me with only the authors whose data I want to look at. Sometimes there's only one author in the cell, sometimes there's multiple (with a comma delimiter between them). What is the most efficient way to filter down to just cells where the authors I'm interested in are contained?
I thought of using a contains filter on the column - Contains([Authors],"XYZ"), but I have about 50 authors and I would prefer not to have to put in 50 of these. Is there a more efficient way to do this? The list of authors I have that I want to filter down to is currently just free text, but I could easily add them to another excel sheet if needed.
I'd suggest using the Text to Columns tool to split out the authors column to individual rows using comma as a delimiter. Then with one row per author use a Join tool (pointing to your list of target authors as the other input) to filter out just the authors you want. Assuming you assigned a unique row ID (i.e. Record ID tool) before you parsed into rows you can now easily join back to the incoming rows where row ID matches row ID. See attached.
Big drawback of using the Join is that you'll find only exact matches, rather than simply "contains" type matches. If you need to use "contains", I'd recommend taking a looking at the Dynamic Formula tool (CReW Macros).