Excel Formulas

Copper Contributor

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.

9 Replies

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. 

@zayn_mir 

Please explain in more detail what you want to do.

You might consider using functions like TEXTBEFORE, TEXTAFTER, and TEXTSPLIT, if available in your version. It's difficult to recommend a specific formula without an example.
Thank you for responding to my query guys. What I want is that if cell A1 has some data e.g. (TTT, LLL,MMM,AVA), I want a formula in cell B1 that takes this data from Cell A1 ( irrespective of the positioning of the data or even if two datas (LLL, AVA) are both populated in Cell A1) and copy only that specific data e.g. (TTT, LLL,MMM,AVA) to Cell B1.

@zayn_mir 

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))
Thank you for providing the formula. Can this formula be used for 15 Terms as the formula you provided can only be used for 4 terms. Moreover does this formula work on Google Sheets as I am unable to implement it on Google Sheets.

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.

Sure. The formula you provided is only recognizing the first 4 columns of "Terms". If I add additional data in Columns 5,6,7,8,9 the formula does not recognize them as "Terms"

@zayn_mir 

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))