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

 

[AkaTrouble]

i think this works

=LEFT(A2,FIND("#",SUBSTITUTE(A2,"\","#",LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))))-0)

 

[pike]

Hello,
You can use this nifty formulae which peterSSs posted in a similar thread question

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

 

[JoeMo]

Here's another way:
Sheet1

*	A	B
1	Root > Technology > Photography > Lenses	Root > Technology > Photography
2	Root > Gifts > Flowers	Root > Gifts
3	Root > Sports > Clubs > Running	Root > Sports > Clubs

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:274px;"><col style="width:217px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B1 =TRIM(MID(SUBSTITUTE(A1," ",REPT(" *",100)),1,(LEN(A1)-LEN(SUBSTITUTE(A1,">","")))*300)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4
EDIT: The REPT should have a single space between the quote marks (no asterisk).

 

[JosephGourvenec]

Lol, I actually thought your signature was meant for me as part of the post! But very very true and I really like it!

 

[JosephGourvenec]

Thank you everyone too it works and now I can make progress

 

[pike]

Hi,
Just curious as which formulas worked as after test all of the posts I could any get Petersss nifty formula to work??

 

[Rick Rothstein]

Assuming your individual text strings are less than 300 characters long and the delimiter is consistently a greater than symbol surrounded by single spaces, then this formula should work as well...

=SUBSTITUTE(A1," > "&TRIM(RIGHT(SUBSTITUTE(A1,">",REPT(" ",300)),300)),"")

 

[JosephGourvenec]

Hi,
Just curious as which formulas worked as after test all of the posts I could any get Petersss nifty formula to work??

Yours did

Thanks again

 

[Peter_SSs]

.. this formula should work as well...

=SUBSTITUTE(A1," > "&TRIM(RIGHT(SUBSTITUTE(A1,">",REPT(" ",300)),300)),"")

High risk!

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