VBA code for Text to Column




Here is a basic VBA code for converting Text to Column that works across multiple columns in an Excel workbook. You can edit this code to turn on (or off) various options such as DataType (xlFixedWidth or xlDelimited), TextQualifier and which delimiters to use while converting from text to column.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Sub text_to_column()
Application.ScreenUpdating = False
On Error Resume Next
For Each wksht In ActiveWorkbook.Worksheets
    For Each col In wksht.Columns
        Columns(col.Column).TextToColumns _
        Destination:=Cells(1, col.Column), _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=True, _
        Semicolon:=False, _
        Comma:=False, _
        Space:=False, _
        Other:=False, _
        FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True
    Next col
Next wksht
Application.ScreenUpdating = True
End Sub


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=""> <s> <strike> <strong>


Comments and Trackbacks

  1. VBA Code - Multiple Column Text to Column Conversion | da TaB is On wrote:

    […] a bit of VBA code to convert Text to Column which will work across multiple columns selected together at one […]

  2. Custom Format to Catch Numbers Appearing as Text | Excel & VBA - da Tab Is On wrote:

    […] left is to identify the columns that contain numbers masquerading as text and then to use the text to column option on each of the columns one-by-one. If the number of columns is large, eyeballing the data […]



Translate

Chinese (Simplified)DutchEnglishFilipinoFrenchGermanHebrewHindiItalianJapanesePortugueseRussianSpanish

treeemap software for excel