Written by Allen Wyatt (last updated February 29, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and 2021
When using the tools available from the ribbon to export a worksheet, as a CSV file, Arkadiusz noted that he can specify that he wants to use a semicolon (;) as a field delimiter. However, if he saves a CSV file using a macro (FileFormat:=xlCSV or xlCSVWindows), then he cannot specify a semicolon as a delimiter.
This works this way by design in VBA. The Excel implementation of the export routines for VBA always use whatever the Windows regional settings are to determine how items in a CSV should be separated. Specifically, the routine looks at the List Separator field for the delimiter. This means that you can, if desired, change the delimiter to a semicolon by changing the List Separator setting in your regional settings configuration.
If you don't want to change the regional settings, then you can instead write your own macro that will output the file in any way you desire. Consider, for a moment, the following macro, which will output the file:
Sub CreateFile() Dim sFName As String Dim Rows As Long Dim Cols As Long Dim J As Long Dim K As Long Dim sTemp As String Dim sSep As String sSep = ";" 'Specify the separator to be used sFName = ActiveWorkbook.FullName If Right(sFName, 5) = ".xlsx" Then sFName = Mid(sFName, 1, Len(sFName) - 5) sFName = sFName & ".txt" Open sFName For Output As 1 With ActiveSheet 'Number of rows to export is based on the contents 'of column B. If it should be based on a different 'column, change the following line to reflect the 'column desired. Rows = .Cells(.Rows.Count, "B").End(xlUp).Row For J = 1 To Rows sTemp = "" Cols = .Cells(J, .Columns.Count).End(xlToLeft).Column For K = 2 To Cols sTemp = sTemp & .Cells(J, K).Value If K < Cols Then sTemp = sTemp & sSep Next Print #1, sTemp Next J End With Close 1 sTemp = "There were " & Rows & " rows of data written " sTemp = sTemp & "to this file:" & vbCrLf & sFName Else sTemp = "This macro needs to be run on a workbook " sTemp = sTemp & "stored in the XLSX format." End If MsgBox sTemp End Sub
This macro opens a text file that has the same name as your workbook. It then steps through each row and starts putting together a string of the cell contents. (This is put into the sTemp variable.) Each cell has a semicolon placed between it, as defined by the sSep variable. Each row's concatenated values are stored in the text file, and when done the text file is closed. The routine is very quick, and when done it displays a message indicating how many rows were exported to the file.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9243) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021. You can find a version of this tip for the older menu interface of Excel here: Specifying a Delimiter when Saving a CSV File in a Macro.
Best-Selling VBA Tutorial for Beginners Take your Excel knowledge to the next level. With a little background in VBA programming, you can go well beyond basic spreadsheets and functions. Use macros to reduce errors, save time, and integrate with other Microsoft applications. Fully updated for the latest version of Office 365. Check out Microsoft 365 Excel VBA Programming For Dummies today!
There are a variety of ways that you might want to count the cells in your worksheet. One way is to figure out how many ...
Discover MoreIf you need to know whether a particular value is odd or even, you can use this simple formula. Designed to be used in a ...
Discover MoreIf you need to insert the current time, with seconds, then you'll need the macro discussed in this tip. It's easy to use ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments