Fix the data extracted into 1 single column in an excel report

Depending of the system tool I am using, it happened already that in the tool, the information is showing nicely but when I extracted the data into excel, all the columns are put into 1 single column. I remember the first time it happened, I was thinking that I was doing something wrong, so I repeat it again and got the same result.

All the cells in the column A had the information but they are not separated. If in the tool, each data were divided properly into its respective columns, extracting into excel, put them all in 1 single one which was the column A.

From this moment, I have 2 choices:

  • Do it manually but when I had 20 cells or more, it is not easy, well, even if there is only 1 cell
  • Or use the “text to columns” option.

Imagine I have this:

fix data single column excel

So to make the data appear correctly:

  • 1. Select the whole column, in this case, the column A
  • 2. Click on “data” then “text to columns”

fix data single column excel
  • 3. Select “delimited” or “fixed width” then click “next”. Select based on your data and the description, in this example, the one suiting the best is “delimited”
fix data single column excel
  • 4. Select one of the options then click “next”. Again select the one fitting better, in this example, the data are separated by “semicolon”
fix data single column excel fix data single column excel
  • As we can see, the result preview is quite promising
  • 5. Keep the “general” option then click “finish”

fix data single column excel

And this is the final result:

fix data single column excel

As you can see, now, all data are correctly separated and put in their corresponding columns for each row.

Interesting Management