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

Formulas and Functions

14 Replies

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

    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))
    • mtarler's avatar
      mtarler
      Silver Contributor
      Sep 15, 2021
      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.
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Sep 15, 2021

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

    • Joel_Bauer's avatar
      Joel_Bauer
      Copper Contributor
      Sep 17, 2021
      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
  • 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-"))))

         

         

Resources