When working with CSV files in Excel, you may need to customize the delimiter or ensure that dates are formatted correctly. Usually, saving a file as a CSV is straightforward, but if the default options don’t meet your needs, you can create one from scratch using the TEXTJOIN function. For example, if you prefer semicolons as delimiters, you can use the TEXTJOIN function to do this.
Table of contents
YouTube: Excel CSV change delimiter and date format.
Creating a CSV with a Custom Delimiter
Select the cell where you want to create your custom CSV format. Enter =TEXTJOIN(“;”, FALSE, A1:J1) (assuming your data is in columns A to J as below). This formula uses a semicolon as the delimiter and includes empty cells (very important as a blank column should still show the delimiter).
You can now copy the formula down for all rows in your data range. This will apply the custom delimiter to the entire dataset. Once your data is formatted correctly, you can save the file as a CSV file. What we prefer is to copy the entire range, open Notepad, paste the data and save the file with a .csv extension. When you open this file in Excel, it will recognize the semicolons as delimiters.
Formatting Dates Correctly in CSV
Excel may change the format of dates when saving to CSV, which can be problematic if you need a specific format. As shown below, the date on Excel (column A) looks different in the CSV column.
To ensure your dates remain formatted correctly, use the TEXT Function. In a new column, use the TEXT function to format your dates. For example, =TEXT(A2, “yyyy/mm/dd”) will format the date in yyyy/mm/dd format. Drag the formula down to apply it to all date cells.
You can now use the TEXTJOIN function again, but this time include the newly formatted date column. Text1 being the date you formatted above and Text2 is row of data excluding the date which in this case is B2 to J2. So your formula will be =TEXTJOIN(“;”, FALSE, N2, B2, J2).
Copy the formatted data, paste it into Notepad and save the file with a .csv extension. The dates will remain in the desired format when opened in Excel.
Tips and Troubleshooting
- Check for Empty Cells: Ensure that empty cells are handled correctly by setting ignore empty to FALSE in the TEXTJOIN function.
- Consistent Delimiters: Ensure all data is consistently separated by the chosen delimiter to avoid issues when importing the CSV file.