Dec 06 2020 08:07 PM
Dec 06 2020 08:07 PM
I'm currently using the following formula to extract the nth field (n=7) from a text string located in cell A1 and delimited by » Char(187):
It works, but is there a simpler formula that is more inefficient as I am using the formula many times?
A1 sample: »44165»No»44166»»Design & implement a new XXX system»»Insert Name»
Excel 2016 & Windows, but would prefer open platform solution (eg Excel for web); no vba please
I have looked around and also tried:
TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))), (N-1)*LEN(A1)+1, LEN(A1))).
This formula is simpler, but doesn't seem to work on large strings > 256 chars
FILTERXML("<t><s>" & SUBSTITUTE(SUBSTITUTE(E51,"&","&"),CHAR(187),"</s><s>") & "</s></t>","//s["&E50&"]")
This formula has reserved chars (eg &) and does not work on web excel
Dec 06 2020 10:37 PM - edited Dec 06 2020 10:47 PM
@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.
Dec 06 2020 11:26 PM