Paste CSV into Excel

Convert CSV to Excel

Copy pasting CSV (Comma Separated Values) is pretty easy in Microsoft Excel‌ !

What is a CSV file (Comma Separated Values) on Wikipedia

In short : use the Data > Text to Columns option.

How to open csv file in excel with columns

Starting with a CSV file, like the following one, open it in Excel by using the standard Excel file opening, using keyboard shortcut CTRL+O, or by opening it in Windows Explorer with right clic and selection Open with Excel 2010 / Excel 2013 / Excel 2016, depending on the version you have installed.

Each row has been put in one single cell, while you might want them to be separated by columns, therefore the upcoming steps.

Convert csv to excel 2016

Select the column containing the CSV data spreadsheet that has just been pasted into an open Excel spreadsheet.

Then click on the Data > Text to Columns option, which will allow Excel to convert CSV to columns from a single column.

In the Convert Text to Column Wizard, step 1, leave the default option, Delimited, as the CSV file usually delimits cells with a character, such as comma, tabulation, pipe, or semicolon.

In the Convert Text to Column Wizard, step 2, select Comma as a Delimiters (or another one if necessary, like semicolon ; or pipe |).

When the right Microsoft Excel‌ csv delimiter will have been chosen, the CSV comma delimited Excel will be displayed properly, as below, with data from CSV into Excel columns as below example.

In the Convert Text to Column Wizard, step 3, you might want to change all the non numerical columns formatting to Text, this to avoiding loosing leading zeros for example.

CSV data format

As the comma delimited file format is text only, if it is storing any column with numbers, MS Excel‌ might recognize at this step these columns as numerical for example.

In some cases, it might be correct - if the column really only contains numeric values.

But in other cases, it might be wrong. Excel bases its detection of the column format on the first 100 rows, but it might be that a column has numbers only in the first 100 rows, but contains text afterwards.

If the format Text has not been chosen, then they will be detected as numbers, and an error will be displayed.

Another case, if a number like a barcode is stored, for example numbers 001234, then, Excel will detect them as numbers and store only 1234, while they should be stored as text 001234.

What is leading zero on webtopedia

Simply click on the first column, then while holding Shift butting, click on the last one of your selection - you can also add columns to the selection by holding Ctrl and clicking on the column - then select Column data format as Text to format them all as text and lose no data.

Column format can always be fine tuned as necessary afterwards.

Before hitting the Finish button, in case you get the error message There's already data here. Do you want to replace it ?, or to avoid getting it.

Select another Destination cell, and click on an empty cell, either in the current excel sheet, or in another excel sheet columns.

You can then click Finish - and voilà ! Your CSV values have been pasted properly in Excel cells.

What is a CSV file

A CSV (or Comma Separated Values), is a plain text file which contains the equivalent of a spreadsheet, or a table, stored as plain text. Each cell is typically separated by a comma, therefore the name, but another character can be used, for example tabulation or semicolon.

How to force excel to open csv files with data arranged in columns

This is not possible directly. Data has to be opened as explained above, and then arranged to columns.

Convert csv to excel without opening

This is not possible. CSV file first has to be opened in Excel, converted properly to columns using Excel built-in options, and then saved as Excel file.

How to copy paste in excel sheet

The above trick can also be used to paste CSV data in Excel ! Simply paste the whole CSV file in an open Excel spreadsheet, and convert it to Excel columns.

  Buy Microsoft Office 2016

Complete 2019 Excel for Beginners in video

About the author - Yoann
Yoann is an International Business Consultant, facilitator and coordinator. He worked more than 10 years in global business, supply chain, financial, SAP ERP projects, Web and SEO optimization for various industries, such as logistics, cosmetics, consumer goods, beauty care, or fashion, in more than 30 countries around the globe.

Comments (0)

Leave a comment