Trim from end by delimiter ">"

[JosephGourvenec]

Hi All,

I need some help to acheive triming from the end by the first ">" delimiter from the end in a structure for example:



Root > Technology > Photography > Lenses
Root > Gifts > Flowers
Root > Sports > Clubs > Running
Root > Insurance > Car


What I would like to acheive is having a formula that simply takes the last characters off e.g. " > Lenses " so the final result is just "Root > Technology > Photography"

Example with formula from above:


Root > Technology > Photography
Root > Gifts
Root > Sports > Clubs
Root > Insurance



Thanks in advance everyone fro being #awesome

 

[JosephGourvenec]

High risk!

eg "Root > Insurance > Caravans & Cars > Car" --> "Root > Insuranceavans & Cars"

I've not had time to check all but I will do for sure.

But thank you too

 

[Peter_SSs]

I've not had time to check all but I will do for sure.

But thank you too

It would be safer to use one of the earlier methods that actually locates that last ">"

peterSSs posted in a similar thread question

I'd say that must have been quite a long time ago. Note that because of this particular data structure, that formula leaves an extra space on the end of the result which I doubt is desirable. So for this I would now use

=TRIM(LEFT(A1,FIND("|",SUBSTITUTE(A1,">","|",LEN(A1)-LEN(SUBSTITUTE(A1,">",""))))-1))