Excel Text Manipulation II

Copper Contributor

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:

  1. Use the MID function to get a ‘word’ if you know its staring position and length. For instance =MID([@Animals],22,8) will display ‘a-Beagle’ in column E Row 2.
  2. Use the FIND function within the MID function to get the first ‘word’ starting with ‘a-‘; if you know its length.  For instance

         =MID([@Animals],FIND("a-",[@Animals],1),16) will display ‘a-Cocker Spaniel’ Column E row 5.

  1. Use the  FIND function within the MID function within the CONCATENATE function to get two ‘words’ if you know the length of first word, a position after the first ‘a-‘ and before the second ‘a-‘, and the length of the second ‘word’. For instance

         =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

14 Replies

@Joel_Bauer 

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

@Joel_Bauer 

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).

@Hans Vogelaar 

Looks like a solution that snb would have made. :)

 

@Detlef Lewin 

Yes, I see what you mean! <grin>

@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))
oh wait. I just notice you don't have excel 365 so forget that response as you won't have the LET() function. This could actually be converted to your version but the size of the equation would be large to say the least.

@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

apparently TEXTJOIN() was introduced in 2019 version

@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 Hans,
Thanks so much for your response, but I need some help implementing it.
Unfortunately I do not know how to add your @(Animals) function or any function a .xslm workbook.
Can you tell me or can you point me to some useful documentation?
Also I have an issue with the .xslm workbook you sent me.
I get an “#NAME?” error in column E when I hit return in the formula bar. Is this error due to the fact that the ‘@Animals’ function is not installed in my excel version?
Joel

@Joel_Bauer 

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.

S0762.png

Did you allow macros when you opened the workbook?

 

See Create custom functions in Excel for info on creating a VBA function.

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
Hans,
Thank you so much.
Now this function is working for me in my 2013 Excel version when I use the suffix .xlsm.
I tried and I guess somehow I allowed macros.
Also I can modify your function a bit as necessary.
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)
@Hans Vogelaar 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")