Formula to Parse a String in a Cell to Return Last Occurance

[acseric]

Hi All,

I have a cell that contains a number of nodes that are delimited by pipes (|). There are not a fixed number of nodes in the cell.

I need a formula to return the value between the last 2 pipes.

Example:

one|two|three|four|five Return: four
one|two|three|four Return: three
one|two|three Return: two

Any help with this would be greatly appreciated.

Eric

 

[BarryL]

Hi, try

=LEFT(TRIM(RIGHT(SUBSTITUTE(G43,"|",REPT(" ",1000)),2000)),SEARCH(" ",TRIM(RIGHT(SUBSTITUTE(G43,"|",REPT(" ",1000)),2000)))-1)

 

[63falcondude]

Try =TRIM(LEFT(RIGHT(SUBSTITUTE(A1,"|",REPT(" ",100)),200),100))