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


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.

Community Boss

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

Nathan Rudman, Anaplan Model Builder

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



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