Formatting CSV File in Excel (With 2 Examples)

When we are working with pieces of information such as names, addresses or any product information, we sometimes keep them as a text file. This type of text file is called a CSV file. But we may need to format these CSV files for importing in Excel to keep it more precise. Therefore, in this article, we will learn formatting CSV file in excel with 2 examples.

Download Practice Workbook

Download this sample copy to practice by yourself.

What Is CSV File?

The term CSV holds the full form of Comma Separated Values in which any data is stored as a simple text split by some specific separators. In Excel, working with CSV files is very common among its regular users. Any kind of file is identified as a CSV file when the file name contains the .csv extension. Otherwise, when you open the file through Text Editor, you will see that the texts are split by commas.

2 Examples of Formatting CSV File in Excel

In this section, we will format a CSV file that has texts with certain separators. For better understanding, we will explain the process with 2 examples. So without any delay, let’s hop into the process of formatting the CSV file in excel.

Example 1: Format CSV File with Comma in Excel

As the first example, we will format a CSV file in excel that has commas in it. To do the task, follow the process below.

• In the beginning, prepare your text string with commas like this to create a table with headers.

• Then, open a new Excel workbook.
• Here, go to the Data tab and select From Text/CSV.

• Next, you will get a new window to select the CSV file.
• Here, click on the file on your device and select Import.

• Following, you will notice a preview window with the texts.
• In this window, select the File Origin as 65001: Unicode (UTF-8).

• Along with it, select the Delimiter as Comma as we have commas as the separator in the CSV file.
• Lastly, click on Load to close the window.

• Finally, you will see a new table as formatted from the CSV file.

• To change the Sales Amount format, select the cell range D3:D7.
• Then, right-click on it and select Format Cells.

• After this, select Accounting from the Number section and choose Symbol according to the CSV file.

• Lastly, hit OK and you will get the final output.

Read More: How to Open CSV with Delimiter in Excel (6 Simple Ways)

Similar Readings

• [Solved:] Excel Is Opening CSV Files in One Column (3 Solutions)
• Excel VBA to Read CSV File into Array (4 Ideal Examples)
• How to Convert CSV to XLSX Command Line (with Easy Steps)
• Excel VBA: Read a Text File Line by Line (6 Related Examples)
• Excel VBA to Import CSV File without Opening (3 Suitable Examples)

Example 2: Format CSV Data with Comma & Quotation Mark

Let us take the similar text file in this second example. But this time we will use both commas and quotation marks as the separators. Now format this CSV file in excel following these steps.

• First, create the CSV file with commas and quotation marks.

• Then, import this file from the Data > From Text/CSV tab.
• Following, you will see the preview table.
• Here, change the File Origin and Delimiter just like the image below.

• Lastly, you will get the formatted CSV file in excel like this.

Read More: How to Open Notepad or Text File in Excel with Columns (3 Easy Methods)

How to Save CSV File in Excel

As we know the process of formatting CSV files in excel, let us learn how to save an excel file to CSV format. To do this, simply go through the following steps.

• First, go to the File tab in your prepared excel file.

• Then, choose Save As from the left side panel.

• Following, select CSV UTF-8 (Comma delimited) as the File Type.

• Lastly, click on the Save button to save the excel file in a CSV format.

Read More: How to View CSV File in Excel (3 Effective Methods)

Things to Remember

• Make sure, there is no space between values and commas in the CSV file.
• Header rows are required in the text file before formatting.
• For saving the file in CSV format, keep the format of the values matched.

Conclusion

Finally, we are at the end of our article on formatting CSV file in excel. Here we described it with 2 examples. We also described the process to save an excel file into a CSV file. Let us know your feedback on this tutorial. Keep an eye on ExcelDemy for more articles like this.

Related Articles

• How to Compare 2 CSV Files in Excel (6 Easy Ways)
• Excel VBA: Merge Multiple CSV Files into One Workbook
• CSV File Not Opening Correctly in Excel (4 Cases with Solutions)
• Merge CSV Files into Multiple Sheets in Excel (with Easy Steps)
• Difference Between CSV and Excel Files (11 Suitable Examples)
• Convert CSV to Excel Automatically with Easy Steps