Split text after the /

[Sriram Iyengar]

Hi All,

Here is a sample of the text that I have
1) Adhesives & Sealants / Caulk, Sealants, & Fillers / Concrete & Asphalt Repair
2) Safety / Personal Protective Equipment (PPE) / Gloves & Hand Protection / Gloves / Cut- & Puncture-Resistant Work Gloves

I need to extract all the data after the last "/" into a new column but cannot use text to column since some of the rows have a different count of the # of levels (eg1 has 3 levels & eg. 2 has 4 levels

 

[shrivallabha]

Try following formula assuming your data is in cell A2:
=TRIM(RIGHT(SUBSTITUTE(A2,"/",REPT(" ",199)),199))

 

[Sriram Iyengar]

THANKS !! That helped. Is there a similar formula that i can use if I need to extract the data between any of the two "/"

 

[Haseeb A]

Hello Sriram,

Here is one way:

=TRIM(MID(SUBSTITUTE("/"&A2,"/",REPT(" ",LEN(A2))),1*LEN(A2),LEN(A2)))

Change red highlighted to, 2 for 2nd, 3 for 3rd etc....for the instances of /

 

[Sriram Iyengar]

Thanks Haseeb !!!

 

[shrivallabha]

Here's a link for explanation which you may find useful:
http://www.excelfox.com/forum/showt...ext-String?s=dd118fb45b30fe458691324f4388b248

My formula is a version to suit your needs.