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.