Splitting a string with multiple delimiters in Excel.

Often when using Excel, especially when viewing CSV file exports from other system, the need arises to transform the existing values into separate values, each residing within their own cell.

Below is a small recordset of Sales people records with their Sales Region (one digit numeric code) and their Sales Type (one digit letter code).

In this example, I will split the sales persons’ sames (last, first), their sales region, and sales type each into their own column. The advanced portion of this article involves splitting a value not based on one delimiter (comma) but ignoring the first one.

First, I will create three next target columns for the transformation next to the existing A column.

Within the first row of the new B column I will paste the following formula and press [ENTER].

Within the first row of the C column I will paste the following formula and press [ENTER].

Within the first row of the D column I will paste the following formula and press [ENTER]. 

Now I have one row with transformed values.

Next, I will batch-duplicate the formulas in each column to every cell in that cell by double-clicking the lower-right cross-hairs which appear when I move my mouse over it.