Archive for June 19th, 2013

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.

Continue reading...