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

Separating text basis Column Header

Shyam131989

New Member
Hi Team,

Am looking to extract set of comments from a column basis column header. The comment's text length may vary and also in the same cell two different comments would have been captured for same header. Please find enclosed sample file for your reference. Could you please help me in splitting the data.
 

Attachments

Peter Bartholomew

Well-Known Member
Are you sure you really need to do this? The exercise stretched both the latest version of Excel 365 and me.
I defined a couple of Lambda functions:
Code:
AnalyseNotesλ
= LAMBDA(String,
    LET(
        separatedString, REDUCE(
            string,
            headers & ":",
            LAMBDA(return, hdr, SUBSTITUTE(return, hdr, "|" & hdr))
        ),
        list, TEXTSPLIT(separatedString, ":", "|"),
        tag,  CHOOSECOLS(list, 1) & ":",
        text, CHOOSECOLS(list, 2),
        MAP(headers, ExtractTextλ(tag,text))
    )
);

ExtractTextλ
= LAMBDA(tag,text, LAMBDA(hdr,
        TEXTJOIN(CHAR(10), , FILTER(text, tag = hdr & ":", "No " & hdr))
  ));
and called the code from the worksheet using
Code:
= REDUCE(headers, Comments,
      LAMBDA(table,string, VSTACK(table, AnalyseNotesλ(string)))
  )
The formula spills from the top left cell showing the word 'Date'
81107
 

Attachments

bosco_yip

Excel Ninja
Another formula option for Excel 2019 and above

Create and define name as in:
NameRefers to
Data=$A5
Plan=$B$4&":"
Design=$C$4&":"
Test=$D$4&":"
SplitDelimiter=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Data,Plan,"</b><b>"&Plan),Design,"</b><b>"&Design),Test,"</b><b>"&Test)

Then,

In B5, formula copied across right and down:

=TEXTJOIN(CHAR(10),,TRIM(SUBSTITUTE(SUBSTITUTE(FILTERXML("<a><b>"&SUBSTITUTE(SplitDelimiter,B$4,"<r/>"&B$4&" ")&"</b></a>","//b[r]")," :",": "),B$4," "&B$4)))

81116
 

Attachments

Top