Forum Discussion

Joel_Bauer's avatar
Joel_Bauer
Copper Contributor
Sep 15, 2021

Excel Text Manipulation II

Device: PC/Windows 10 Microsoft Office Home and Business 2013 Excel Product ID: 00196-20943-85146-AA714   Dear Community,  Here is my second posting on this subject. By myself I am making a bit ...
Formulas and Functions
mtarler's avatar
mtarler
Silver Contributor
Sep 15, 2021

Joel_Bauer Here is another solution not as elegant as the previous but may help give you some ideas on techniques you can use.  Here is the formula:

=LET( source,[@Animals],
     filter_key, "a",
     length,LEN(source),
     list_expanded, SUBSTITUTE(source,",",REPT(" ",length)),
     array, TRIM(MID(list_expanded,SEQUENCE(length-LEN(SUBSTITUTE(source,",",""))+1,,1,length),length)),
     filtered, FILTER(array,LEFT(array,2)= filter_key&"-"," "),
     TEXTJOIN(", ",TRUE,filtered))

Resources