VBA: Convert text to columns for all worksheets in Excel

A small Visual Basic for Applications (VBA) script which will loop through all sheets in an Excel workbook and convert text to columns.

I recently merged a large number of comma-separated values (CSV) files into a single Microsoft Excel XLSX file, but the data inside each sheet was still in the CSV format.

To fix this quickly, I used the a VBA script which loops through every worksheet in a workbook and performs a text to columns operation.

The script works in a similar way to the manual procedure that can be performed within Excel using the Convert Text to Columns wizard. The options that present themselves in the wizard are encoded in the script and can be altered to suit the format of the data.

Screen capture of Convert Text to Columns wizard in Microsoft Excel. — The “Convert Text to Columns” wizard in Microsoft Excel gives a number of options for converting delimited text into columns.

Instructions

Step 1

Open the Excel sheet that needs to be modified.

Step 2

Press Alt + F11 or otherwise go to the Developer ribbon and choose the “Visual Basic” icon if the Developer Tab is enabled.

Step 3

In the Visual Basic for Applications toolbar, go to Insert > Module

Screen capture of Visial Basic for Applications — Inserting a module in Visual Basic for Applications.

Insert the following code into the module:

Sub Text2Columns()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
    Select Case UCase(ws.Name)
        Case "MASTER", "DATA"
            'do nothing
        Case Else
            ws.Columns(1).TextToColumns Destination:=ws.Range("A1"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
				Tab:=False, _
				Semicolon:=True, _
				Comma:=False, _
				Space:=False, _
				Other:=False, OtherChar:="|", _
				FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1)), _
                TrailingMinusNumbers:=True
    End Select
Next ws
Application.ScreenUpdating = True
End Sub

Step 4

Edit the script as required:

If data is tab-separated, let Tab:=True
If data is semicolon-separated (;), let Semicolon:=True
If data is comma-separated (,), let Comma:=True
If data is space-separated ( ), let Space:=True
If data is separated by another character, let Other:=True and specify the character in OtherChar:=""

These options can be combined if required.

Step 5

Press F5 to run the macro to execute the convert text to columns function across the entire workbook.

Comments

7 responses to “VBA: Convert text to columns for all worksheets in Excel”

On 23 July 2016, KMJA wrote:

Is there a way to do this but only for one worksheet? Also make it so that if the data changes due to a formula it will automatically replace the only columns with the new split columns?

On 6 July 2017, Aniruddh Tyagi wrote:

Can you let me know what is “Master” & “Data” here . this code is not working for me.

On 6 July 2017, Aniruddh Tyagi wrote:

Can you please let me know what is “Master” & “Data” here . this code is not working for me.

On 20 July 2017, Brian wrote:

Hello,

Just wanted to say thank you for posting this formula.

When I try using “Other”, for a file with an apostraphe delimiter (“), it returns an error:

“Compile error:
Expected: End of statement”

Here is the line I used ==> (Other:=True, OtherChar:=”””, _)
I also tried it with just a single apostraphe ==> (Other:=True, OtherChar:=”, _)

However, both resulted in this error message. Please help!

Thank you!

On 21 March 2020, MB wrote:

Hi Everyone,

To make the code work, you just need to change the “other” variable from “false” to “true”. This is because in the example given, the author was, in fact, using an “other” delimiter, in this case the pipe (“|”), so it couldn’t be “false.”

So if you switch the code from…
Other:=False, OtherChar:=”|”,

To the below, then it will work!
Other:=True, OtherChar:=”|”,

On 10 December 2020, Joseph wrote:

The code is very helpful but I wish there was a way to exclude first row which for me should remain intact. It has figure values and is difficult to reconstruct for those ending with ,000

Thanks.

On 7 June 2021, Asu16 wrote:

Thank you very much! :)