Free Practice WorkbookNeed to ship data from Excel into a clean, portable CSV file that works in apps, databases, and web tools? This guide shows practical ways to go from a worksheet to a correctly formatted CSV. The attached workbook gives you sample data, helper formulas, and a finished text to CSV preview so you can follow along and reuse the setup in your own files.
Key Takeaways
- Use CSV UTF-8 if you need accented characters or non English text.
- Quote fields and double any quotes inside a field to prevent breaks.
- TEXTJOIN with SUBSTITUTE builds reliable CSV lines directly in a sheet.
- Regional settings can change the delimiter, check your system list separator.
- Power Query and VBA help when you need repeatable exports or many files.
Table of Contents
What is a CSV and why it matters
A CSV file stores rows of data as plain text, with each field separated by a delimiter. Most tools expect commas, some regions use semicolons. Because CSV is plain text, formatting does not carry over, only the values do. That makes it a compact way to exchange data between systems. It also means you must handle quoting correctly when a field has commas, quotes, or line breaks. The standard pattern is simple, put the field in double quotes and double any internal quotes.
How to Convert Text to CSV Files in Excel
Save As CSV from Excel
Open your data table in Excel. Keep only the columns you want to export.
Go to File, Save As, choose a location.
In Save as type, pick CSV UTF-8 (Comma delimited) or CSV (Comma delimited).
Click Save.
Common Mistakes and how to fix them
Wrong encoding. Accented characters appear as question marks. Use CSV UTF 8 when saving.
Broken rows in target systems. A field includes a comma or line break and the row splits. Quote every field or at least any field that contains a comma, quote, or line break.
Numbers change appearance. Large IDs lose leading zeros or dates reformat. Use TEXT to control number and date formatting in the formula method. For Save As, convert sensitive IDs to text before export.
Extra blank rows or columns. Hidden columns or trailing spaces slip into the file. Clean the data first and export only the used range or a defined table.
Wrong delimiter for your region. Commas are expected but your system uses semicolons. Check your Windows list separator or switch to a formula based method where you set the delimiter.
Bonus tips and advanced scenarios
Use a custom delimiter. Replace the comma inside TEXTJOIN with a semicolon or a pipe if a target app needs it. Example, TEXTJOIN(";", TRUE, ...).
Export multiple CSVs by group. Use Power Query to group by Region, then create a query per Region and load each to a separate sheet that you save as a separate CSV.
Preserve leading zeros. For fields like account IDs, store them as text or use TEXT([@ID],REPT("0",LEN([@ID]))) in your formula based output.
Automate with a macro button. Add a shape, assign the export macro to it, and keep an .xlsm copy for one click exports.
Use cases
Finance and accounting exports. Move journal lines, invoices, or payments to CSV for ERP or accounting imports.
Typical fields: Date, Account, Debit, Credit, Currency, Department, Project, DocNumber, Memo.
Balancing checks: add a control cell with =SUM([Debit])-SUM([Credit]) and require zero before export.
Number formats: enforce two decimals with =TEXT([@Amount],"0.00"), avoid currency symbols in the value column, keep Currency in a separate field.
Operations and logistics. Share order pick lists or shipment plans with warehouses that require CSV.
Typical fields: OrderID, SKU, Qty, Bin, ShipByDate, Carrier, ServiceLevel, Notes.
Date control: =TEXT([@ShipByDate],"yyyy-mm-dd") to match the WMS expected format.
Sorting: sort by Bin to reduce picker travel time before exporting.
Education and training. Build sample datasets for classroom demos, then export to CSV for students to load into Power Query, PivotTables, or SQL practice.
Create multiple difficulty levels by changing row counts with =TAKE() or =INDEX().
Seed realistic text cases, for example commas, quotes, and line breaks, so students can learn correct CSV handling.
FAQ
Which format should I choose, CSV UTF 8 or CSV?
Pick CSV UTF 8 when you have non English characters or accents. Plain CSV may default to ANSI on some systems and can corrupt characters.
Why are semicolons used instead of commas on my CSV?
Excel follows your operating system list separator. In some locales it is a semicolon. Change the system setting or use a formula method to set your own delimiter.
How do I keep leading zeros in CSV?
Store the field as text in Excel or convert with TEXT in the formula based method, then export. CSV has no cell formats, only text.
Can I export only selected columns?
Yes. Create a staging table with only the columns you need, then Save As CSV or build the CSV lines from that table with TEXTJOIN.
How do I handle line breaks in fields?
Always quote the field. The formula method shown wraps every field in quotes and doubles any quotes inside, which keeps line breaks intact.
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.
30 Days - Full Access 
