Find the position of nth time a character appears in a sting

NilanjanDutta_*20530
NilanjanDutta_*20530
in Modeling

I want to pull the description details from the History log. I have calculated the number of commas before the Description column which is 3 but I am not able to find the position after the 3rd comma to extract the Description data for multiple rows.

 

Row 1 - ID,Date/Time (UTC),User,Description,Security Change,Previous Value,New Value

Row 2 - 2077909,17-10-2020 14:24,tabc@abc.com,Bulk Change,,,

 

Please share your approach.

Tagged:

Answers

  • nathan_rudman
    nathan_rudman

    I don't understand what you mean by position ? the character number in order to extract the string ?

    you need to know the position of the 3rd and the 4th and extract the difference with MID()

  • NilanjanDutta_*20530
    NilanjanDutta_*20530
    Yes Nathan, I am looking for a formula that can identify the character number for the 3rd and 4th comma so that I can extract the string and the formula stays dynamic across rows.

    For eg-
    Row 1 - The position of the 3rd comma is 24 and 4th is 36
    Row 2 - The position of the 3rd comma is 37 and 4th is 49
    I need help in building the formula to identify the above positions
  • Misbah
    Misbah

    @NilanjanDutta 

     

    Assuming I have four commas within the string, this is how I would get to know the positioning of those commas

     

    First Line Item=> FIND(",", NAME(ITEM(List)),0)

    Second Line Item=> FIND("," NAME(ITEM(List)),First Line Item +1)

    Third Line Item=> FIND(",", NAME(ITEM(List)),Second Line Item +1)

    Fourth Line item=> FIND(",", NAME(ITEM(List)), Third Line Item +1)

     

    You see how NAME(ITEM(List)) is getting repeated in every formula  - create separate line item for it and refer that line item in the each of these forumulae

     

    Hope this helps

    Misbah

Categories