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

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.

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