Forum Discussion
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 ...
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
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")
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")