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

Highlighted
Occasional Contributor

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,tabc@abc.com,Bulk Change,,,

 

Please share your approach.

3 REPLIES 3
Highlighted
Community Boss

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

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
Highlighted
Occasional Contributor

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

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
Highlighted
Master Anaplanner/Community Boss

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

@nildutta 

 

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