Forum Discussion

anupambit1797's avatar
anupambit1797
Steel Contributor
Jan 14, 2026
Solved

Headers split from notepad++ to Excel

Dear Experts,                    Greetings! I have a data from attached notepad ++ and want headers like in below format, using text delimiter as " | " didn't helped.    
BI & Data Analysis
excel
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Jan 17, 2026

    Hi anupambit1797​ 

    possible to use some legacy formulas?

    This is the kind of key info./requirement you should always provide upfront & when talking about legacy formulas you should also say which version of Excel is in use. Not knowing I assumed 365/Web

    Below is a 365 LAMBDA option where no string is hard-coded

    #1 When opening the text file from within Excel make sure you select options "Delimited" then "Comma" in the Import Wizard, this to ensure you get the data as a single column

     #2 With a file like the one you shared data is then imported in sheet 'headers', range A1:A6. The full reference is therefore: headers!A1:A6

    #3 In the attached workbook I stored the LAMBDA in the Name Manager and named it RESTRUCT_HEADERS

    You have 2 options to run the LAMBDA. Easiest one is in B8 above:

    =RESTRUCT_HEADERS( headers!A1:A6 )

    or, as in B2 above (formula EDITED):

    =LAMBDA(input_range,
  LET(
    data,         DROP( input_range, 2 ),
    delim,        "|",

    CLEANSTR,       LAMBDA(string, TRIM( CLEAN( string ) ) ),
    MAKEBOTTOMROW,  LAMBDA(string, TAKE( DROP( TEXTSPLIT( string, delim ),, 25 ),, 22 ) ),

    seqOutCols,   SEQUENCE(, 22 ),
    dataRow1,     CLEANSTR( TAKE( DROP( TEXTSPLIT( INDEX( data, 1, 1 ), delim ),, 22 ),, 2 ) ),
    outRow1,      XLOOKUP( seqOutCols, {6,17}, dataRow1, "" ),
    dataRow2,     CLEANSTR( TAKE( DROP( TEXTSPLIT( INDEX( data, 2, 1 ), delim ),, 24 ),, 2 ) ),
    dataRow2x,    HSTACK( dataRow2, dataRow2 ),
    outRow2,      XLOOKUP( seqOutCols, {4,9,15,20}, dataRow2x, "" ),
    outRow3,      MAKEBOTTOMROW( INDEX( data, 3, 1 ) ),
    outRow4,      MAKEBOTTOMROW( INDEX( data, 4, 1 ) ),

    VSTACK( outRow1, outRow2, outRow3, outRow4 )
  )
)( headers!A1:A6 )

    PS Revised the Power Query approach to simplify it and remove dependency on previously hard coded strings. Feel free to re-download

Lorenzo's avatar
Lorenzo
Silver Contributor
Jan 18, 2026

anupambit1797​

Variant + Conditional Formatting

 

// RESTRUCT_HEADERS
=LAMBDA(input_range,
  LET(
    data,         DROP( input_range, 2 ),
    delim,        "|",
    outColumns,   22,

    HdrStartAt,   LET(
                    inRow2,   INDEX( input_range, 2, 1 ),
                    splitted, TRIM( TEXTSPLIT( inRow2, "|") ),
                    firstStr, TAKE( TOROW( IF( LEN(splitted), splitted, NA() ), 2), 1 ),
                    FIND( firstStr, inRow2 )
                  ),
    shiftedData,  MID( data, HdrStartAt, LEN(data) -HdrStartAt ),

    MAKE_ROW,     LAMBDA(string, TRIM( TAKE( DROP( TEXTSPLIT( string, delim ),, 2 ),, outColumns ) ) ),
    COMPACT_ROW,  LAMBDA(array, TOROW( IF( LEN(array), array, NA() ), 2 ) ),
    GET_VALUES,   LAMBDA(array, row_num, take_cols,
                    TAKE( COMPACT_ROW( DROP( TRIM( TEXTSPLIT( INDEX( array, row_num, 1 ), delim ) ),, 2 ) ),, take_cols )
                  ),

    seqOutCols,   SEQUENCE(, outColumns ),
    outRow4,      MAKE_ROW( INDEX( shiftedData, 4, 1 ) ),
    outRow3,      MAKE_ROW( INDEX( shiftedData, 3, 1 ) ),
      stringRow3,   TAKE( COMPACT_ROW( outRow3 ), 1, 1 ),
      posStrRow3,   TOROW( (XMATCH(outRow3, stringRow3) * seqOutCols) -1, 2 ),
      shiftPosRow3, posStrRow3 +2,
    outRow2,      XLOOKUP( seqOutCols, posStrRow3, GET_VALUES( shiftedData, 2, 4 ), "" ),
    outRow1,      XLOOKUP( seqOutCols,
                    TOROW( IF( MOD(shiftPosRow3, outColumns / 2), shiftPosRow3, NA() ), 2 ),
                    GET_VALUES( shiftedData, 1, 2 ), ""
                  ),

    VSTACK( outRow1, outRow2, outRow3, outRow4 )
  )
)

 

Resources