• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to extract multiple instances of a pattern (between "-"'s) within a single cell

IeuanCilgwri

New Member
82670

The above is an extract of a very large value in a single cell, I would to extract into a cell ALL the instances that occur between the "-" - so the above would return LFN,LFN,LEM,LEL - thank you!
 
With 365 you could try things like
Code:
= LET(
    splitOnHyphen, TEXTSPLIT(value,,"-"),
    twoColumns,    WRAPROWS(splitOnHyphen,2),
    codeInstances, TAKE(twoColumns,, 1),
    TEXTJOIN(",",,codeInstances)
  )
which picks up on the alternating pattern of hyphens.

There are some add-ins that implement RegEx, one of which is Charles Williams's FastExcel (c)
Code:
= Rgx.MID(value,"\-\w{3}\-",0)
 
Try,

In B, enter formula:

=TEXTJOIN(",",,FILTERXML("<a><b>"&SUBSTITUTE("@"&A2,"-","</b><b>")&"</b></a>","//b[string-length(.)=3]"))

82684
 
Back
Top