Excel - Function to show specific string length

psc1

New Member
Joined
Nov 27, 2011
Messages
15
Office Version
  1. 2010
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

Cell Formulas
RangeFormula
M5:M8M5=D12
P5:P8P5=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)))
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 27, 2011
Messages
15
Office Version
  1. 2010
excel 2010 on this PC
Will update my details now thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows
Thanks for the, how about
+Fluff 1.xlsm
MNOP
4ValueReturn setDelimiterAnswer
5Antiques-collectables / Alcohol-related / Spirits-related / Bourbon3/Antiques-collectables / Alcohol-related / Spirits-related
6Antiques-collectables / Alcohol-related / Spirits-related / Gin2/Antiques-collectables / Alcohol-related
7Antiques-collectables / Alcohol-related / Spirits-related / Other4/Antiques-collectables / Alcohol-related / Spirits-related / Other
8Antiques-collectables / Alcohol-related / Spirits-related / Rum2/Antiques-collectables / Alcohol-related
Main
Cell Formulas
RangeFormula
P5:P8P5=TRIM(LEFT(SUBSTITUTE(M5,O5,REPT(" ",100),N5),100))
 
Solution

psc1

New Member
Joined
Nov 27, 2011
Messages
15
Office Version
  1. 2010
Thanks Fluff
It works a treat :)
You are incredibly fast in replying 🚀😅😅
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,920
Messages
5,638,995
Members
417,061
Latest member
thematulaak

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top