Excel - Function to show specific string length

[psc1]

This was started on a different post but I realized it was published 15 years ago so reposting..

I would like to display the entire length of the string up to the point chosen by the "Return set" ?
(this would include previous delimiters)
Would someone please be able to modify the "Answer" formula to suit?

Something along the below example:
Value: a/b/c/d/e
Return Set: 2
Delimiter: /
Answer: a/b

I have attached String example below

Book1
	M	N	O	P
4	Value	Return set	Delimiter	Answer
5	Antiques-collectables / Alcohol-related / Spirits-related / Bourbon	3	/	Spirits-related
6	Antiques-collectables / Alcohol-related / Spirits-related / Gin	2	/	Alcohol-related
7	Antiques-collectables / Alcohol-related / Spirits-related / Other	4	/	Other
8	Antiques-collectables / Alcohol-related / Spirits-related / Rum	2	/	Alcohol-related
Sheet1

Cell Formulas
Range	Formula
M5:M8	M5	=D12
P5:P8	P5	=IF(O5=1,LEFT(M5,FIND(O5,M5,1)-1),IF(N5=LEN(M5)-LEN(SUBSTITUTE(M5,O5,""))+1,RIGHT(M5,LEN(M5)-FIND("~",(SUBSTITUTE(M5,O5,"~",LEN( M5)-LEN(SUBSTITUTE(M5,O5,"")))),1)),MID(M5,FIND("~",SUBSTITUTE(M5,O5,"~",N5-1),1)+1,FIND("~",SUBSTITUTE(M5,O5,"~",N5),1)-FIND("~",SUBSTITUTE(M5,O5,"~",N5-1),1)-1)))

 

[Fluff]

What version of Excel are you using & what platform?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[psc1]

excel 2010 on this PC
Will update my details now thanks

 

[Fluff]

Thanks for the, how about

+Fluff 1.xlsm
	M	N	O	P
4	Value	Return set	Delimiter	Answer
5	Antiques-collectables / Alcohol-related / Spirits-related / Bourbon	3	/	Antiques-collectables / Alcohol-related / Spirits-related
6	Antiques-collectables / Alcohol-related / Spirits-related / Gin	2	/	Antiques-collectables / Alcohol-related
7	Antiques-collectables / Alcohol-related / Spirits-related / Other	4	/	Antiques-collectables / Alcohol-related / Spirits-related / Other
8	Antiques-collectables / Alcohol-related / Spirits-related / Rum	2	/	Antiques-collectables / Alcohol-related
Main

Cell Formulas
Range	Formula
P5:P8	P5	=TRIM(LEFT(SUBSTITUTE(M5,O5,REPT(" ",100),N5),100))

 

[psc1]

Thanks Fluff
It works a treat
You are incredibly fast in replying ???

 

[Fluff]

You're welcome & thanks for the feedback.