Excel formula (not VBA) to extract delimited string?

[joeu2004]

I have the abstract string "firstString/secondString/thirdString/fourthString" in C2.

I want to use a single Excel formula (not VBA; no helper cells) to extract "secondString". Any ideas?

Note that "*String" is any arbitrary sequence of characters, not including "/". So, the formula must depend on only the delimiters ("/"), not the length of substrings or the characters themselves in between.

Also, the solution must be limited to Excel 2003 functions.

Off-hand, the best I can do is:

=REPLACE(LEFT(C2, FIND("/",C2, FIND("/",C2)+1)-1), 1, FIND("/", C2), "")

Can we do better?


-----
I'm helping someone in another forum. The above meets his needs. But for "extra credit", the ideal formula can be easily adapted to extract "thirdString". Sigh, my formula would be cumbersome to adapt for that purpose.

 

[Fluff]

In that case it appears that MS have got it wrong.

<section class="ocpArticleTitleSection"> [h=1]SUBSTITUTE function[/h] </section> <section class="supAppliesToSection" aria-label="This article" dir="ltr"> Excel for Office 365 Excel for Office 365 for Mac Excel for the web Excel 2019 Excel 2016 Excel 2019 for Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 for Mac Excel for Mac 2011 Excel Starter 2010 Less

</section>

https://support.office.com/en-gb/article/substitute-function-6434944e-a904-4336-a9b0-1e58df3bc332

 

[joeu2004]

In that case it appears that MS have got it wrong. https://support.office.com/en-gb/article/substitute-function-6434944e-a904-4336-a9b0-1e58df3bc332

Not exactly. If you look at the IF support page, we see the same list, not mentioning Excel 97, let alone Excel 2003, to wit:

Excel for Office 365 Excel for Office 365 for Mac Excel for the web Excel 2019 Excel 2016 Excel 2019 for Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 for Mac Excel for Mac 2011 Excel Starter 2010

Do you really believe that means the IF function was introduced in Excel 2007?!

My guess: That list identifies only versions of Excel that MSFT "supports". I'm not sure that still includes Excel 2007. But it definitely does not include Excel 2003 and before.

Moreover, MSFT support pages should not be trusted to be accurate. Anything that seems "surprising" should be tested. That goes double for the sanitized support pages that we find online these days, which omit critical information and sometimes simply misstates it.

 

[Peter_SSs]

I'm a big fan of explaining assumptions, for the benefit of the unsuspecting user. The TRIM/REPT paradigm presumes that the delimited substrings ("fields") do not contain spaces that need to be retained. A valid assumption in my immediate case. But more generally, I would write:

=SUBSTITUTE(MID(SUBSTITUTE(A1, delimiter, REPT(unusedChar,99)), fieldNumber*99-98, 99)), unusedChar, "")

where "unusedChar" can be CHAR(1), if necessary.

Some comments ..

1. You have an errant parenthesis in your formula

2. If you are a stickler for including assumptions, then you would need to either
a) Include assumptions your formula contains about the overall length of the original string & sub-strings (refer to cells B11:B14 below which are supposed to be extracting the 11th to 14th strings from the column A string), or
b) Make your formula more general by addressing the caution Rick raises towards the end of his article and as covered in posts 3 & 5 of that thread.

I have used that further generalisation, together with your 'space' resolution, in column C

Excel Workbook
	A	B	C
1	aString 1	aString 1	aString 1
2	aString 1/aString 2	aString 2	aString 2
3	aString 1/aString 2/aString 3	aString 3	aString 3
4	aString 1/aString 2/aString 3/aString 4	aString 4	aString 4
5	aString 1/aString 2/aString 3/aString 4/aString 5	aString 5	aString 5
6	aString 1/aString 2/aString 3/aString 4/aString 5/aString 6	aString 6	aString 6
7	aString 1/aString 2/aString 3/aString 4/aString 5/aString 6/aString 7	aString 7	aString 7
8	aString 1/aString 2/aString 3/aString 4/aString 5/aString 6/aString 7/aString 8	aString 8	aString 8
9	aString 1/aString 2/aString 3/aString 4/aString 5/aString 6/aString 7/aString 8/aString 9	aString 9	aString 9
10	aString 1/aString 2/aString 3/aString 4/aString 5/aString 6/aString 7/aString 8/aString 9/aString 10	aString 10	aString 10
11	aString 1/aString 2/aString 3/aString 4/aString 5/aString 6/aString 7/aString 8/aString 9/aString 10/aString 11	aString	aString 11
12	aString 1/aString 2/aString 3/aString 4/aString 5/aString 6/aString 7/aString 8/aString 9/aString 10/aString 11/aString 12	11	aString 12
13	aString 1/aString 2/aString 3/aString 4/aString 5/aString 6/aString 7/aString 8/aString 9/aString 10/aString 11/aString 12/aString 13	aString 12	aString 13
14	aString 1/aString 2/aString 3/aString 4/aString 5/aString 6/aString 7/aString 8/aString 9/aString 10/aString 11/aString 12/aString 13/aString 14	aString 13	aString 14
Extract String

My guess: That list identifies only versions of Excel that MSFT "supports".

That is my understanding.