nth occurrence text function - finds nth time a character occurs in a string

I would like the ability to use a function that allows me to search for the nth time a character or string of characters occurs in a concatenated key.

Currently, we import text string keys from source systems and derive business information from these strings. For instance, we might have the transaction key Customer123_ProductQ_RegionSW_PeriodQ1 (123_Q_SW_Q1). However, the customers, products, regions, or periods might not have the same text length every time/different variables might have the same text (if Product is SW and Region is SW), which makes using a combination of RIGHT, LEFT, MID, FIND, etc embedded in each other complex/difficult to maintain in order to extract this information to columns. However, if I were able to have a function like NthOccurrence(Name(Item('TransactionKey')), "_", 2) to find the beginning position of Region, our formulas would be clearer/easier to maintain.

17
17 votes

In Review · Last Updated

Comments

  • Status changed to: In Review
  • Has anyone found a workaround for this issue?

  • A possible workaround is to employ a different symbol as a delimiter for each segment. Something like "123_Q-SW^Q1". Then the formula doesn't require as much fiddling.

  • For anyone referencing this looking for a solution - you should be able to use the below (screenshots) to fulfill your needs.

     

    Nonetheless I think this is still a good idea and would require less space/line items.

     

    Anaplan Community Image 1.pngAnaplan Community Image 2.png

     

     

  • Status changed to: In Review

Get Started with Idea Exchange


See our Submission Guidelines and Idea Evaluation Criteria, then start posting your own ideas and showing support for others!