How to Extract Text or Numbers from Character Delimited Cell Content in MS Excel

© Ugur Akinci
Character-delimited data is fairly common. Here is an example:

100&4657&23&495&103

or

Apple$Pear$Orange$Cherry$Grapes

Imagine having a MS Excel table with cells populated with such content. How would you separate the text or the numeric values and post them under their own separate columns?
Here is an example of some imaginary data about SEASONAL SALES:

Here is how to extract the seasonal sales figures and post them under seasonal columns.

Two major steps:

(1) Finding out where the delimiting characters are (“/” in this example).
(2) Extracting the contents by using the locations of the delimiting characters.

STEP 1: Finding out where the delimiting characters are

Finding the first “/”:

Use this formula:

=FIND(“/”,A2,1)

In plain English: “Find the first “/” character in cell A2 starting with the first character; then print its location from the left in this cell.”
Result:

Finding the second “/”:

Use this formula:

=FIND(“/”,A2,B2+1)

In plain English: “Find the first “/” character in cell A2 AFTER the location of the FIRST “/”; then print its location from the left in this cell.”
Result:

Finding the third “/”:

Use this formula:

=FIND(“/”,A2,C2+1)

In plain English: “Find the first “/” character in cell A2 AFTER the location of the SECOND “/”; then print its location from the left in this cell.”
Result:

STEP 2: Extracting the contents

(1) Create the columns WINTER, SPRING, SUMMER, FALL.
(2) For WINTER, use the following formula:

=LEFT(A2,B2-1)

In plain English: “Find all the characters in cell A2 to the LEFT of the FIRST “/”; then print the string in this cell.”
Here is the result:

(3) For SPRING, use the following formula:

=LEFT(A2,B2+1,C2-B2-1)

In plain English: “Find all the characters in cell A2 IN BETWEEN the FIRST “/” and the SECOND “/”; then print the string in this cell.”
Here is the result:

(4) For SUMMER, use the following formula:

=LEFT(A2,C2+1,D2-C2-1)

In plain English: “Find all the characters in cell A2 IN BETWEEN the SECOND “/” and the THIRD “/”; then print the string in this cell.”
Here is the result:

(5) For FALL, use the following formula:

=RIGHT(A2,(LEN(A2))-D2)

In plain English: “Find all the characters in cell A2 to the RIGHT of the THIRD “/”; then print the string in this cell.” LEN(A2) calculates the total length of the alphanumeric string in cell A2.
Here is the result:

And here’s how the whole picture looks like after we’re through:

NOTE: This method proves its worth when you have not only just a few but hundreds and even THOUSANDS of lines of delimited data that needs to be separated out.