Excel Tip: Splitting Column Values June 19th, 2013

Vinod Kumar

This is in continuation to tips around Excel. This particular tip has been around in Excel for ages. But just like any other feature in Excel this is yet another nice utility that is lesser known to many.

Let us assume you have the following data set. This is a simple data set and it has Name that has come from some external source. Now we want to split as Lastname and FirstName instead of the usual Name column as shown below.

Now to split the column, create a new column “C” by inserting a new column adjacent to our name column. Next select all the values from Name column. With this selected, goto Toolbar “Data” Tab –> “Text to Columns” under Data Tools section.

Selecting this invokes a wizard which is easy steps. Select “Delimited” and press “Next”.

This is the screen where we will select the delimiter. In our Dataset, the Name field has a space. So we will select “Space” in this screen. And select “Next”.

Now we will be presented with the final screen. This can be used for some Formatting options. In my data, I am going to select the defaults. Press “Next”.

Our final screen shows the split automatically done. The screen would typically look like below. Give the Column “C” as Lastname and we must be done.

This option works wonders when data values are consistent and has just two parts or three parts for ALL data values. Else we will get additional columns and if there is data, a popup appears telling us there are values and do we need to override. Be careful in such cases.

Hope you have used these in the past and I will try to bring in more such tips in our subsequent blog posts.

Share this article

Tags: , , , , , , , , , ,

This entry was posted on Wednesday, June 19th, 2013 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

4 Responses to “Excel Tip: Splitting Column Values”

  1. Avinash reddy says:

    its really superb i tried with it seems good

    thanks a lot you for sharing your knowledge to all

  2. Paras Doshi says:

    Thanks for the Excel Tips!

  3. Beena says:

    New learning for me.


Leave a Reply