Forum Discussion
Split a string by delimited characters and choose nth position in string
Looking for an excel formula that will take a string similar to this Instance >> Chain >> TimeZone >> State >> City >> Number >> Number2 And be able to output only the first "Number" occurren...
BAWrites Just for good measure, here's one more option using a combination of TEXTBEFORE and TEXTAFTER (available in MS365 and Excel for the Web):
=TEXTBEFORE(TEXTAFTER(A1, " >> ", 5), " >> ")
The biggest benefit of this method is that it can accept an entire range of data and spill the results dynamically. For example, if you had multiple text strings in range A1:A9, each following the same delimited pattern, only one formula is needed to extract the nth item for each string:
=TEXTBEFORE(TEXTAFTER(A1:A9, " >> ", 5), " >> ")
Note: if all of the extracted items are in fact numbers, use either the double-negative method (--) or the VALUE function to coerce the results to numeric values.
Also, to handle inconsistencies in the data, where some strings include the "Number2" field, and some do not, set the optional [match_end] argument for TEXTBEFORE to 1 in order to prevent the #N/A error from occurring with strings that end after the first "Number" field. For example:
=--TEXTBEFORE(TEXTAFTER(A1:A9, ">>", 5), ">>",,, 1)
TEXTBEFORE / TEXTAFTER Spilled Results
