Extract part of the string

[#1]

Hi All,

I have Field contains the values with '&'. This '&' could be anywhere in the string, but I want to extract the string between last and second last '&'

for eg. I have string like abc&xyz&pqr&ghi

I want the value pqr

[Kushal_Chawda · Accepted solution]

something like this

= mid(Field, Index(Field,'&',-2)+1, Index(Field,'&',-1) -(Index(Field,'&',-2)+1))

[sunny_talwar]

You can also try this:

=SubField('abc&xyz&pqr&ghi', '&', Index(KeepChar('abc&xyz&pqr&ghi', '&'), '&', -1))

Output is pqr here

[antoniotiman]

=SubField('abc&xyz&pqr&ghi','&',-2)  ->  pqr

[#5]

thanks. all are working

[sunny_talwar]

I agree all solution are working, but I would suggest using ANTONIO‌'s solution as it is the most straight forward of the three.

Best,

Sunny

[Kushal_Chawda]

Even I agree with sunindia , use AntonioTiman solution