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
You can download a sample worksheet with a example of multiple column text to column here or click on the button below:
what’s the explanation/logic for the array arguments in the text to columns structure?
thank you
May 29th, 2011 at 8:35 am