Show string after second or more appearance of specific char

[Inessan86]

Hi All,

I have two columns; one holds the codes for changed fields, the second keeps string of the values for each changed field: 

Field1: ,25,52,12,

Field2: ~abc~def~gh~

I want to see what is the value for field 52 (def). 

I used the following function to retrieve the number of commas up to 52: SubStringCount(TextBetween([Field1],',','52'),',') as CommasCounter (in this case 2)

and now I need to get the value in Field2 after 2 appearances of  ~ symbol. 

Any idea of how doing that? 

Inessa

[tresesco]

You can use the same textbetween() with additional parameter for count, like:

Textbetween(Field2, '~', '~', 2) 

[Kushal_Chawda]

if you need full string after 2 appearances then try below

=mid(Field2,index(Field2,'~',2)+1)

[sunny_talwar]

Is there a reason not to use SubField() function for this

SubField([Field1], ',', 2) & '(' & SubField([Field2], '~', 2) & ')'

[Brett_Bleess]

@Inessan86  You have received 3 possible options on your post, we would greatly appreciate it if you would return to the thread and close it out by using the Accept as Solution button on the post(s) that helped you solve the use case.  If you did something different based upon the posts, you may post what you did instead and then mark that post using the button, but I would encourage to use the Like feature on the post(s) that helped in that case too.

Regards,
Brett

[Inessan86 · Accepted solution]

I used SubField(Field2, '~', SubStringCount(TextBetween(Field1,'',52),',')) and it worked for me. 

Thank you for your reply, guys!