Forum Discussion

anupambit1797's avatar
anupambit1797
Iron Contributor
May 25, 2023

Text After Delimiter

Dear Experts,                      Could you please help in below Query:- In Attached Excel, I want my data like in Column "B" only the Text after 4 spaces(Item xx), then in Column "C" the text aft...
Formulas and Functions
Patrick2788's avatar
Patrick2788
Silver Contributor
May 26, 2023

anupambit1797 

Maybe I'm simplifying things a bit too much (or not understanding the goal here) but if the objective is to pull the text after the leading spaces (which might be 4, 8, 12 spaces, etc). Why not use TRIM?

 

 

=TRIM(A2)

 

 

  • anupambit1797's avatar
    anupambit1797
    Iron Contributor
    May 26, 2023

    Patrick2788Thanks Patrick , but the intention is to put the text with different space in different columns

     

    Br,

    Anupam

    • Patrick2788's avatar
      Patrick2788
      Silver Contributor
      May 26, 2023

      anupambit1797 

      Then I will have to indulge:

       

       

      =LET(
    Level, (LEN(List) - LEN(TRIM(List))) / 4,
    MAKEARRAY(
        ROWS(List),
        7,
        LAMBDA(r, c, IF(c = INDEX(Level, r), INDEX(List, r), ""))
    )
)

       

      • anupambit1797's avatar
        anupambit1797
        Iron Contributor
        May 29, 2023

        Patrick2788Thank you Very much Patrick, this is very cool! Sorry for stupid question though..

         

        List , I understood you used the Name Manager, what does "Level" ,"r" and "c" stands for here? and how to check their definition.. in the Name Manager only "List" is defined,

         

        Thanks & Regards

        Anupam Shrivastava

         

         

Resources