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


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,,Bulk Change,,,


Please share your approach.


  • 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()

  • 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
  • @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