Split a string by delimited characters and choose nth position in string

[BAWrites]

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" occurrence from the string.

Any help is appreciated.

[#2]

@BAWrites 

Let's say the string is in A2.

In another cell, for example B2:

=--INDEX(TEXTSPLIT(A2, " >> "), 6)

This can be filled down.

[#3]

@BAWrites While TEXTSPLIT() will work for you. You can also use FILTERXML() for previous versions of Excel (Excel-2013 or later).

=FILTERXML("<t><s>"&SUBSTITUTE(A1,">>","</s><s>")&"</s></t>","//s[6]")

 Using TEXTSPLIT()...

=CHOOSECOLS(TEXTSPLIT(A1,">>"),6)

[#4]

@BAWrites 

Here's a formula that will extract the first "Number" occurrence from the string:

Excel=MID(A1,FIND("Number",A1)+7,FIND(">>",A1,FIND("Number",A1)+7)-FIND("Number",A1)-7)

Explanation:

1. FIND("Number",A1): Finds the position of the first "Number" within the string.
2. MID(A1,...,...): Extracts a substring from the original string.
3. +7: Skips the characters "Number >" to get to the actual number.
4. FIND(">>",A1,FIND("Number",A1)+7): Finds the position of the next ">>" after the first "Number".
5. -FIND("Number",A1)-7: Subtracts the lengths of "Number >" and the first "Number" to determine the length of the number you want to extract.

Example:

If your string is in cell A1:

Instance >> Chain >> TimeZone >> State >> City >> Number 123 >> Number2

The formula will return:

123

Key points:

• Adjust the cell reference (A1) to match the cell containing your string.
• If the structure of your strings might vary, consider using more robust string manipulation techniques, such as combining FIND, LEFT, RIGHT, and LEN functions.

[#5]

@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