Feb 15 2023 02:16 PM
Hi, I need a formula which can extract specific data from a column e.g (xyz, abc) irrespective of the positioning of the data and the number of times the data is entered in that cell.
Feb 16 2023 09:59 AM
Hello! You've posted your question in the Tech Community Discussion space, which is intended for discussion around the Tech Community website itself, not product questions. I'm moving your question to the Excel space - please post Excel questions here in the future.
Feb 16 2023 10:11 AM
Please explain in more detail what you want to do.
Feb 17 2023 06:52 AM
Feb 17 2023 09:07 AM
Feb 17 2023 09:21 AM
Maybe this:
=LET(str,IF(ISNUMBER(SEARCH(terms,A1)),terms,""),TEXTJOIN(", ",,str))
If case sensitive use:
=LET(str,IF(ISNUMBER(FIND(terms,A1)),terms,""),TEXTJOIN(", ",,str))
Feb 20 2023 06:08 AM
Feb 20 2023 06:22 AM - edited Feb 20 2023 06:22 AM
This formula can be scaled to include 15 terms by updating the named item 'terms'. I don't use Google sheets so I can't comment on if the formula would work in that app.
Feb 20 2023 07:24 AM
Feb 20 2023 08:43 AM
You could make the list of 'term's dynamic. As an example,
Terms
=LET(t,Sheet4!$D$2:$D$1000,nonblank,COUNTA(t),TAKE(t,nonblank))