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.