Forum Discussion
Excel Text Manipulation II
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
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-"))))
- 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- Unfortunately the CONCATENATE() function will not work on arrays. So the FILTERXML() solution will return an array but the CONCATENATE() function will not join them and will only return the 1 in the list. (It does the same with the other equation also but that equation returns a blank for each non-correct entry so you see blanks in each case)
HansVogelaar solution using a macro is your best option if you can't upgrade to a newer Excel. You need to make sure your security allows macros. As he noted, the [@Animals] reference is just a way to reference the corresponding row in the column called "Animals" but to try it out maybe just use the actual cell reference or a text string to make sure it is working:
=GetAnimals(D2,"a") or
=GetAnimals("Elephant,Monkey,Lion,a-Beagle,a-French Poodle,b-Persion,b-Amercan Wirehair","a")