Sep 15 2021 07:51 AM
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 of progress in the way I think I need to go. Please help me continue.
In column D of the enclosed spreadsheet I want to extract all comma delimited ‘words’ starting with “a-“and place them in column E. Similarly I want to place all ‘words’ starting with “b-“ in column F.
Using excel functions, I believe/hope that a program can be written to accomplish my goal.
Note that the ‘Before’ tab holds my original data; the ‘After; holds my desired result; and the ‘Play’ tab shows the results of my experiments with excel functions.
Here is what I have been able to do so far:
=MID([@Animals],FIND("a-",[@Animals],1),16) will display ‘a-Cocker Spaniel’ Column E row 5.
=CONCATENATE(MID([@Animals],FIND("a-",[@Animals],1),8),",",(MID([@Animals],FIND("a-",[@Animals],38),15))) will display ‘a-Beagle,a-French Poodle’ in column E row 6.
Thanks, Joel Bauer
Sep 15 2021 08:12 AM
=IFERROR(TEXTJOIN(",",TRUE,FILTERXML("<y><z>"&SUBSTITUTE([@Animals],",","</z><z>")&"</z></y>","//z[contains(., 'a-')]")),"")
Sep 15 2021 08:20 AM
As an alternative, a very short VBA function:
Function GetAnimals(Animals As String, Letter As String)
GetAnimals = Join(Filter(Split(Animals, ","), Letter & "-"), ",")
End Function
Use like this:
See the attached version (now a macro-enabled workbook).
Sep 15 2021 08:35 AM
Sep 15 2021 09:08 AM - edited Sep 15 2021 09:11 AM
Yes, I see what you mean! <grin>
Sep 15 2021 09:30 AM
@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))
Sep 15 2021 09:32 AM
Sep 15 2021 11:42 AM
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
Sep 15 2021 11:58 AM
Sep 15 2021 01:12 PM
@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-"))))
Sep 17 2021 05:10 AM
Sep 17 2021 06:27 AM
The [@Animals] in the formula is a so-called structured table reference. It points the the cell in the Animals column in the same row.
Did you allow macros when you opened the workbook?
See Create custom functions in Excel for info on creating a VBA function.
Sep 19 2021 08:18 AM
Sep 21 2021 08:27 AM
Sep 21 2021 08:46 AM