Extracting second last data (Delimiter)

[ashwithonline]

Hi All,

Needed help ... I tried LEFT, RIGHT, TEXTBEFORE and TEXTAFTER...but nothing worked

Input	Output
Jagdish,Sreedhar,Vanaja,Shetty	Vanaja

In this the input is a text string seperated by (,) and I want to extract the second last word which is mentioned in the Output column.

The actual data is with almost 17-18 delimiters, but to make it simple I made this simple example above.

Can anybody please help me with this....


Thanks in advance,
Jagdish

 

[ETAF]

i'm sure there is an easier way then this
=DROP(TAKE(TEXTSPLIT(A2,","),,-2),,-1)

this splits the cell
then takes the last 2 items
and drops the last 1
so you get 2nd to last

 

[p45cal]

=TAKE(DROP(TEXTSPLIT(A1,","),,-1),,-1)
or
=LET(a,TEXTSPLIT(A1,","),INDEX(a,COUNTA(a)-1))

 

[AliGW]

This will suffice:

=TEXTBEFORE(TEXTAFTER(A1,",",-2),",")

 

[ashwithonline]

Thanks a lottttt....................everybody...!!!!!!!!!!!!!!!!!!!!

 

[ETAF]

you are welcome

 

[AliGW]

Thanks a lottttt....................everybody...!!!!!!!!!!!!!!!!!!!!

You can LIKE our contributions by clicking on the icons in our posts.