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
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
May 26, 2023

anupambit1797 

One option for shortening formulas is to build them from Lambda functions.  For example a function to determine how many leading spaces exist might be 

= Indentλ(string)

where the formula defining Indentλ
= LET(
    initialCharacter, LEFT(SUBSTITUTE(string," ",""), 1),
    FIND(initialCharacter, string)
  )

 

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

    PeterBartholomew1Can you please explain this by all the steps you perform? probably in any doc with Screen shots of the the steps we need to perform?

     

    Thanks & Regards

    Anupam Shrivastava

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

      anupambit1797 

      The main point is that such formulas only work with Excel 365.  The solutions offered by Lambda are not remotely similar to any traditional spreadsheet formula.  What a named Lambda function does is allow you to show the intent of a formula without exposing anything of how it is achieved.

       

      In fact, I changed the formula 'Indentλ' to

      = LAMBDA(string,
      LET(initialCharacter,  LEFT(TRIM(string), 1), 
      FIND(initialCharacter, string))
  )

      but it does not change anything on the worksheet.  I simply posted to suggest a possible avenue to take to make spreadsheet formulas more intelligible, though not always more concise.  If you don't have 365, then it is back to something more traditional!

      [perhaps not that far back?]

       

      The attached workbook illustrates the type of tricks that are now possible.

Resources