Excel: String Split

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).

1-Excel-String-Split

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.2-Excel-String-Split

Within the first row of the new B column I will paste the following formula and press [ENTER].
3-Excel-String-Split 4-Excel-String-Split

Within the first row of the C column I will paste the following formula and press [ENTER]. 5-Excel-String-Split 6-Excel-String-Split

Within the first row of the D column I will paste the following formula and press [ENTER]. 7-Excel-String-Split 8-Excel-String-Split

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.

9-Excel-String-Split

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s