RIGHT until "/"

[Torstensson]

Hi,

New here at the forum and looking for some help.

I have a couple of thousands strings that looks like this:
/art/telefon/mobiltelefon/varumrken/samsung/samsung-i9100galaxysii/150354.3211/

And this:
/art/dator/laptop/laptop-17-tum/packardbell-easynotelj71sb777nc/149524.3004/

What a want from this is the product name. As you can see it's not always the same amount of columns.

I was thinking of counting from the right to find the third "/" and extract to the second "/".

How is this done?

Thanks in advance!

 

[njimack]

A bit messy but this should work (until someone posts a much better solution!)

=MID(SUBSTITUTE(SUBSTITUTE(A1,"/","~",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-2),"/","#",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-2),FIND("~",SUBSTITUTE(SUBSTITUTE(A1,"/","~",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-2),"/","#",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-2))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(A1,"/","~",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-2),"/","#",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-2))-FIND("~",SUBSTITUTE(SUBSTITUTE(A1,"/","~",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-2),"/","#",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-2))-1)

 

[mikerickson]

Perhaps
=TRIM(RIGHT(SUBSTITUTE(LEFT(SUBSTITUTE(A1, "/", REPT(" ",255), 3),255), "/", REPT(" ",255), 2), 255)

 

[p45cal]

A formula something inbetween mikerickson's and njimack's in rows 1:2, and how it's derived in rows 4:5, as well as a user-defined function in rows 7:8, this last supported by the code below.

Excel Workbook
	A	B	C	D	E
1	/art/telefon/mobiltelefon/varumrken/samsung/samsung-i9100galaxysii/150354.3211/				samsung-i9100galaxysii
2	/art/dator/laptop/laptop-17-tum/packardbell-easynotelj71sb777nc/149524.3004/				packardbell-easynotelj71sb777nc
3
4	/art/telefon/mobiltelefon/varumrken/samsung/samsung-i9100galaxysii/150354.3211/	8	44	67	samsung-i9100galaxysii
5	/art/dator/laptop/laptop-17-tum/packardbell-easynotelj71sb777nc/149524.3004/	7	32	64	packardbell-easynotelj71sb777nc
6
7	/art/telefon/mobiltelefon/varumrken/samsung/samsung-i9100galaxysii/150354.3211/				samsung-i9100galaxysii
8	/art/dator/laptop/laptop-17-tum/packardbell-easynotelj71sb777nc/149524.3004/				packardbell-easynotelj71sb777nc
Sheet

Function SplitMe(StringToSplit, Delimiter, InstanceNoFromRight)
x = Split(StringToSplit, Delimiter)
SplitMe = x(UBound(x) - InstanceNoFromRight)
End Function

 

[Torstensson]

Thanks a lot guys, works perfectly?

 

[njimack]

A formula something inbetween mikerickson's and njimack's in rows 1:2, and how it's derived in rows 4:5, as well as a user-defined function in rows 7:8, this last supported by the code below.
<

Function SplitMe(StringToSplit, Delimiter, InstanceNoFromRight)
x = Split(StringToSplit, Delimiter)
SplitMe = x(UBound(x) - InstanceNoFromRight)
End Function

I really like that function - think I might have to save that to a module in my Personal.xls