Forum Discussion
Text to Columns for Multiple Columns at Once
If you already have blank columns next to your data, you can use a simple macro to automate your text to column conversions. I suggest recording the conversion of one column, then generalizing the recorded macro because the text to columns method is a little complicated.
The code below was based on a recorded macro. It assumes you select the cells to be converted, that every other column will be converted, and that you already have the necessary blank columns.
Sub TextToColumnator() Dim rg As Range Dim i As Long, n As Long Application.ScreenUpdating = False Application.DisplayAlerts = False Set rg = Selection n = rg.Columns.Count For i = 1 To n Step 2 rg.Columns(i).TextToColumns Destination:=rg.Cells(1, i), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True Next Application.DisplayAlerts = True End Sub
If you already have blank columns next to your data, you can use a simple macro to automate your text to column conversions. I suggest recording the conversion of one column, then generalizing the recorded macro because the text to columns method is a little complicated.
The code below was based on a recorded macro. It assumes you select the cells to be converted, that every other column will be converted, and that you already have the necessary blank columns.
Sub TextToColumnator()
Dim rg As Range
Dim i As Long, n As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set rg = Selection
n = rg.Columns.Count
For i = 1 To n Step 2
rg.Columns(i).TextToColumns Destination:=rg.Cells(1, i), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Next
Application.DisplayAlerts = True
End Sub
Oh wow! I got the macro you shared to work, thank you! I was initially trying to record my own macro instead, which I'd still like to figure out. But for my current purposes, yours works exactly how I need!
:)
It is also possible to use a macro to insert the blank columns.
Select your data, then run the macro. It will add blank columns after each column in your selection.
Sub InsertBlankColumns() Dim rg As Range Dim i As Long, n As Long Application.ScreenUpdating = False Set rg = Selection.EntireColumn n = rg.Columns.Count For i = n To 1 Step -1 rg.Columns(i + 1).EntireColumn.Insert Next End Sub- Thank you very much!