Separating text basis Column Header

[Shyam131989]

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.

 

[Peter Bartholomew]

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:

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

= REDUCE(headers, Comments,
      LAMBDA(table,string, VSTACK(table, AnalyseNotesλ(string)))
  )

The formula spills from the top left cell showing the word 'Date'

 

[bosco_yip]

Another formula option for Excel 2019 and above

Create and define name as in:

Name	Refers 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)))