Excel > Functions

Text to columns in Excel

How to convert csv data into columns in MS Excel?



In previous post, we learnt about seprating string from sepecific characters in Excel. In this post, we shall learn about converting CSV (comma separated values) data into columns in Excel. 

This post is equally helpful in case data is not comma separated but simicolumn, space, tab or any other characters separated.

In below example, we have a comma separated data from A1 to A5. Select all the rows and select 'Text to Columns' command from DATA tab on the ribbon.

Text to Column command in Excel

This opens up 'Convert Text to Columns Wizard' as shown below. In this wizard, select the file type/data type we have to convert to. We have comma delimited / seprated data so let the 1st radio button selected.

Convert Text to Columns wizard in Excel

Now select the delimited character from 'Delimiters' list. As our data is 'Comma' seprated so check the 'Comma' checkbox. We get preview of our final data below under 'Preview' box. If it is looking good press 'Next >' else adjust the data or Delimiters.

select delimiters in text to column in excel

Now, choose the data type of each column. Select column heading and then choose the data type from 'Column data format' heading to format that column data.

Format data in text to column in excel

Now press 'Finish' button to show the formatted data at the same place like below.

Text to column command result in Excel

Quickies Wink

If we want formatted data to appear at some other location, click right side icon of 'Destination' textbox and select the target cell. Now, clicking 'Finish' will bring the formatted data to the new location.

 Views: 401 | Post Order: 28




Write for us