Excel formula to pull data between two delimiters in cell with multiple delimiters

You can use this formula to pull data between any two of multiple delimiters in a single cell in Excel.

=MID(A2,FIND(CHAR(1),SUBSTITUTE(A2,"|",CHAR(1),4))+1,(FIND(CHAR(1),SUBSTITUTE(A2,"|",CHAR(1),5))-FIND(CHAR(1),SUBSTITUTE(A2,"|",CHAR(1),4)))

Enter the relative position of the delimiter in front of the value you want to pull at the end of the first SUBSTITUTE formula; the delimiter which comes after this value at the end of the second SUBSTITUTE formula, and then enter the position of the delimiter in front at the end of the third SUBSTITUTE formula.

This formula can be helpful when you're parsing out of a lot of delimiters and want to avoid splitting data into too many columns with the Text to Columns tool.

So, in this example the value 'Dublin' appears between the 4th and 5th instances of the | pipe delimiter.

Excel formula to pull data up to the first delimiter

You can use two simple formulas in Excel to pull all of the data to the left of the first occurrence of a delimiter in a cell, and then all of the data that comes to the right of that delimiter, even though there are several additional occurrences of the delimiter.

In this example we have text separated by pipe delimiters in cell A2. Enter this formula in cell B2:

=LEFT(A2,FIND("|",A2)-1)

The FIND formula locates the character position of the first delimiter and then the LEFT formula extracts the text up to this point - or one character less.

In cell C2 enter this formula:

=RIGHT(A2,LEN(A2)-FIND("|",A2))

This repeats the steps in the first formula, but subtracts the result form the total character count in the cell.

Use VLOOKUP to find multiple values in an array

VLOOKUP will only find the first value in an array for the term you search for. If you want to find multiple values in the array for the successive times that the searched for value appears, you can do so with the help of Excel's COUNTIF formula.

In this example in columns M through O we have shipping dates, destinations, and products. In columns A and B we have a list of shipping dates and destinations. We want to find each time a shipment was made on the date listed in column A and see if one of the shipments was made to the destination in column B.

In column L, we enter this formula:

=M2&COUNTIF($M$2:$M2,M2)

The COUNTIF formula searches for how many times a value appears in column M upwards to row 2, and then adds the result to the end of the date. And so, each of the dates is numbered and unique values are generated.

In column C we enter this formula:

=IFNA(VLOOKUP($A2&COLUMNS($C$1:C1),$L$2:$O$3000,3,0),"")

VLOOKUP searches for the date in column A. Enter the range to columns L to O at the end of the formula. We use '3' to return the data in the third column in this array - the city. The value in COLUMNS refers to column in which the formula is entered.

Pull the formula in C2 to the right, (press CTRL + R) and the multiple cities shipped to on the dates listed in column A will be generated in the columns to the right.