Categorized | excel macro and vba

VBA Code – Multiple Column Text to Column Conversion

Here’s a bit of VBA code to convert Text to Column which will work across multiple columns selected together at one go.

Here’s the code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66

Sub Multi_Column_Text_To_Column()

Dim selected_range, selected_range_individual_column() As Range
Dim one_to_how_many_columns, col_count As Long

Set selected_range = Selection
On Error GoTo err_occured:

'-------------------------------------------------------------------------------------
'one_to_how_many_columns value = Number of colums that a single column should be split into
'Provide a sufficiently large value so as to prevent overlaps and overwriting
'-------------------------------------------------------------------------------------
one_to_how_many_columns = 10

Application.DisplayAlerts = False
If Not (TypeName(selected_range) = "Range") Then End
ReDim selected_range_individual_column(selected_range.Columns.Count - 1) As Range

For col_count = LBound(selected_range_individual_column) To UBound(selected_range_individual_column)
Set selected_range_individual_column(col_count) = selected_range.Columns(col_count + 1)
'MsgBox "Value = " & selected_range_individual_column(col_count).Cells(1, 1).Value
Next col_count

'Begin Text to Column conversion process by starting from Right and proceeding left
For col_count = UBound(selected_range_individual_column) To LBound(selected_range_individual_column) Step -1

If Application.WorksheetFunction.CountIf(selected_range_individual_column(col_count), "<>") = 0 Then GoTo next_loop:

'-------------------------------------------------------------------------------------
'DataType = xlDelimited or xlFixedWidth
'-------------------------------------------------------------------------------------
'If Data Type = xlDelimited then one has to specify the delimiting characters
' Change the boolean values for various delimiting characters such as :
' ConsecutiveDelimiter, Tab, Semicolon, Comma, Space and Other tokens as per requirement
'If Data Type = xlFixedWidth then one has to specify the widths of the fields using the FieldInfo Array.
' This example specifies three widths for splitting into five columns with each array
' bit containing the cumulative sum of chars till the beginning of each word
' You will have to edit and modify (add more or delete) these values as per need
'-------------------------------------------------------------------------------------

selected_range_individual_column(col_count).TextToColumns _
Destination:=selected_range.Cells(selected_range.Row, one_to_how_many_columns * col_count + 1), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=True, _
Other:=False, _
FieldInfo:=Array( _
Array(0, 1), _
Array(3, 1), _
Array(6, 1), _
Array(12, 1), _
Array(17, 1) _
), _
TrailingMinusNumbers:=True

next_loop:
Next col_count
err_occured:
Application.DisplayAlerts = True
End Sub

Microsoft Office Excel can convert only one column at a time. The range can be many rows tall but no more than one column wide. Tray again by selecting cells in one column only.

In the code above you can change:
one_to_how_many_columns = Determines how many columns should be used as a gap to place the (split/converted) data when two consecutive columns are converted.
DataType = xlDelimited or xlFixedWidth (The text to column feature has two modes – Character Delimited and Fixed Width.)
TextQualifier = xlDoubleQuote or xlSingleQuote or xlNone
Tab = True or False
Semicolon = True or False
Comma = True or False
Space = True or False
TrailingMinusNumbers = True or False
ConsecutiveDelimiter = True or False