Forum Discussion
Formula for extracting nth field from long delimited text string
Katoomba Not sure if this will meet you demands, but you indicated that you would like a solution that works in Excel for the Web. Well, then perhaps the LET function will come in handy.
=IFERROR(
LET(
myText, A2,
Nth,B2,
Marker, "»",
Repl1,"|",
Repl2,"||",
MarkedText, SUBSTITUTE(SUBSTITUTE(myText,Marker,Repl1,Nth),Marker,Repl2,Nth),
From, FIND(Repl1,MarkedText)+1,
To, FIND(Repl2,MarkedText),
NrOfchar, To-From,
ExtractedText, MID(MarkedText,From,NrOfchar),
ExtractedText
),
I had some difficulties with the CHAR(187) part of your formula so I just entered the "»" as the marker to search for. Also used pipe-symbols to mark the beginning and the end of the Nth field with.
The formula is certainly not shorter than yours, but it more efficient and easier to read and maintain. A working example is located in the attached workbook.
Now, if you are not bound to the Excel for the Web, consider using Text-To-Columns on the data ribbon, and then select the Nth column from the output. Works in every Excel version I'm aware of except for the on-line version. You'll need an add-in to achieve it there, I believe, but I may be mistaken.
I hope you'll find it helpful.
Riny_van_Eekelen Thank you for your response and my apologies for not replying earlier as I only picked up the discussion now.
Thank you for your suggestion, but your formula is probably a bit long and complicated for my purposes
I am currently using a large delimited string (eg >15K) to pass data between > 20 networked workbooks and a central master workbook (ie a single string greatly simplifies links between workbooks). I am then using the MID formula many thousands of times to automatically extract data at the other end. MID works surprisingly well (with some performance overhead), but I am hoping that there is a simpler standard formula that ideally would only require parameters to be included once or uses fewer SUBSTITUTES.
I will ultimately need to write up and handover the workbooks to someone else and would like all formulae to be simple enough for someone with middling Excel skills to understand - ie no VBA or array formulae
Excel provides the Textjoin and Concatenate formulae to create delimited strings but unfortunately offers no simple formula to extract data from a delimited string
Excel on the Web compatibility is not required (at this point) but is a nice to have