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]

Slightly shorter
=MID(C2,FIND("/",C2)+1,FIND("/",C2,FIND("/",C2)+1)-FIND("/",C2)-1)

 

[DanteAmor]

How about

=TRIM(MID(SUBSTITUTE(C2,"/",REPT(" ",256)),256,256))

 

[joeu2004]

Thanks for the ideas, Fluff and DanteAmor.

 

[FormR]

Hi, another more generic option for parsing delimited strings can be found here courtesy of Rick Rothstein..

http://www.excelfox.com/forum/showthread.php/333-Get-Field-from-Delimited-Text-String

 

[Peter_SSs]

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".

Probably covered by Rick in FormR's link but I didn't look.
This adaptation of Dante's formula easily allows that by changing the red number

=TRIM(MID(SUBSTITUTE(C2,"/",REPT(" ",255)),255*2-254,255))

 

[DanteAmor]

Thanks for the ideas, Fluff and DanteAmor.

I'm glad to help you. Thanks for the feedback.

 

[joeu2004]

Thanks FormR and Peter. Rick's article is definitely a keeper. Just an observation.... 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.

 

[Fluff]

I would also point out (for anyone that doesn't know) the substitute function first appeared in xl2007, so does not fit your criteria of working in xl2003.

 

[joeu2004]

the substitute function first appeared in xl2007, so does not fit your criteria of working in xl2003

That is incorrect!

First, when we save as "xls" in Excel 2010, the compatibility checker does not flag the use of SUBSTITUTE. In contrast, it does flag the use of COUNTIFS.

Second, I still have Excel 2003 on my WinXP computer, and I can use the SUBSTITUTE function therein. The screen tips shows the same usage of SUBSTITUTE in Excel 2003 as it does in Excel 2010.

Finally, when I use Excel 2003 to open the "xls" file that I saved with Excel 2010, the COUNTIFS function appears as _xlfn.COUNTIFS (unsupported function), whereas SUBSTITUTE appears as-is (supported function).

QED.