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
Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
Sep 15, 2021

Joel_Bauer 

=IFERROR(TEXTJOIN(",",TRUE,FILTERXML("<y><z>"&SUBSTITUTE([@Animals],",","</z><z>")&"</z></y>","//z[contains(., 'a-')]")),"")
  • Joel_Bauer's avatar
    Joel_Bauer
    Copper Contributor
    Sep 15, 2021

    Detlef_Lewin 

    Hi Detlef,

      Thanks for your response, however your formula is not working for me. Very well could be my bad.

       In the enclosed spreadsheet I pasted your formula into the formula bar for Cell E2. The Cell remained blank. Same for Cell E3.

       Can you tell me why it’s not working?

    Joel

    • mtarler's avatar
      mtarler
      Silver Contributor
      Sep 15, 2021

      Joel_Bauer  so here is a version of that FILTERXML you can try:

      =IFERROR(CONCATENATE(FILTERXML("<y><z>"&SUBSTITUTE([@Animals],",",", </z><z>")&"</z></y>","//z[contains(., 'a-')]")),"")

      the problem is that in my version it gives a #SPILL! error but your version wont have spill.  It works for me if I use CONCAT but like TEXTJOIN those aren't available in your version.  Also, unless the last item is included it will have a hanging comma added on the end.

      I also created a version of the formula i did earlier that should work in 2013 but it also has similar issues as the above with concatenate wanting to spill and potential comma at the end:

       

      =CONCATENATE(TRIM(MID(SUBSTITUTE([@Animals],",",","&REPT(" ",1000)),ROW(OFFSET(A1,0,0,LEN([@Animals])-LEN(SUBSTITUTE([@Animals],",",""))+1))*1000-999,1000*(LEFT(TRIM(MID(SUBSTITUTE([@Animals],",",REPT(" ",1000)),ROW(OFFSET(A1,0,0,LEN([@Animals])-LEN(SUBSTITUTE([@Animals],",",""))+1))*1000-999,1000)),2)="a-"))))

       

       

      • Joel_Bauer's avatar
        Joel_Bauer
        Copper Contributor
        Sep 19, 2021
        Hi Mtarler,

        Thanks so much for your help.
        =IFERROR(CONCATENATE(FILTERXML("<y><z>"&SUBSTITUTE([@Animals],",",", </z><z>")&"</z></y>","//z[contains(., 'a-')]")),"")
        This formula is the best so far for me. It did not give an error when I copied it into my version of the workbook, and displayed the first ‘word’ starting with ‘a-‘. But it did not display subsequent ‘words’ starting with ‘a-‘. In your enclosed workbook Cell G2 (in the play tab) should be ‘a-Beagle, a-French Poodle,” not just ‘a-Beagle’. I am hoping we are close to success. Can you see how to fix it? The trailing comma is okay.

        Joel
    • mtarler's avatar
      mtarler
      Silver Contributor
      Sep 15, 2021
      apparently TEXTJOIN() was introduced in 2019 version

Resources