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.

multiple-column-text-to-column-vba-code title=

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

You can download a sample worksheet with a example of multiple column text to column here or click on the button below:

multiple-column-text-to-column


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. io wrote:

    what’s the explanation/logic for the array arguments in the text to columns structure?

    thank you

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel