Search code examples
excelexcel-formula

A formula to find both text in asterisks and find text next to parentheses

enter image description hereExample of requestLooking for a way to potentially combine my formulas and haven't had any success. I look at large, variable sheets where I only want to review the Active Description, but the data that I'm given lists the Active Description with all of the previous iterations (Inactive Description). Unfortunately, it lists the Active Description in multiple formats.

Currently I am doing the following to get a column that only has the Active Descriptions:

To only show text in asterisks:

=MID(B2,FIND("*",B2)+1,FIND("*",B2,FIND("*",B2)+1)-FIND("*",B2)-1)

To only show the Active Description when the description has the active next to a (type):

Text to columns with line break as a delimiter, then combining the results with the Active Description into one source column (issue is that it the number of line breaks can be highly variable). To only show the text that isn't in parentheses:

=TRIM( TEXTBEFORE(B1,"("))

And finally for the descriptions that only have active in the column, I'm copying them over as is.

Is there one if statement that I can write to do it all at once?

Solution 
  • =LET(a,{"*";"(type)"},
     b,TEXTSPLIT(B5,CHAR(10)),
TEXTSPLIT(TOCOL(IFS(FIND(a,b),b),2),a,,1))

    First a is declared: an array of the asterisk sign and (type).

    Next b is declared: an array of the cell value split by end of line character CHAR(10).

    If we find a in b it errors on strings not containing any of the a strings. If the string does contain any of the two, it returns that string including the a string(s) found in it.

    If we split that by a and tell TEXTSPLIT to ignore empty strings, the a string(s) contained will be deleted (splitter out). Resulting in your Active string.

    Or in one spill: =MAP(B2:B5,LAMBDA(m,LET(a,{"*";"(type)"},b,TEXTSPLIT(m,CHAR(10)),TEXTSPLIT(TOCOL(IFS(FIND(a,b),b),2),a,,1))))

    Including error handling for no string meeting the conditions and comma separated in case multiple returns per cell: =MAP(B2:B5,LAMBDA(m,LET(a,{"*";"(type)"},b,TEXTSPLIT(m,CHAR(10)),ARRAYTOTEXT(IFERROR(TEXTSPLIT(TOCOL(IFS(FIND(a,b),b),2),a,,1),"")))))