Formatting CSV File in Excel (With 2 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.


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.


Formatting CSV File in Excel: 2 Suitable Examples

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.

Format CSV File with Comma in Excel

  • 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).

Format CSV File with Comma in Excel

  • 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.

Format CSV File with Comma in Excel

  • 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 Keep Leading Zeros in Excel CSV Programmatically


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.

Format CSV Data with Comma & Quotation Mark

  • 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.

CSV File Excel Formatting

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

Note: You can use any of the following separators in the CSV file.
  • , = comma
  • TAB = the tab key
  • ; = semi-colon
  • | = pipe
  • ^ = carat

Read More: How to Fix CSV File in Excel


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.

How to Save CSV File in Excel

  • 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: Paste Comma Separated Values into Excel


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.

Download Practice Workbook

Download this sample copy to practice by yourself.


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.


Related Articles


<< Go Back to Import CSV to Excel | Importing Data in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sanjida Mehrun Guria
Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo