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

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


Comments and Trackbacks

  1. Karen wrote:

    Can you show me an alternative code that will run on just 1 worksheet – my workbook has over 50 worksheets but only one of them needs to have the text to column macro run on it.

    Thanks.
    Karen

  2. Rasik Vekaria wrote:

    You are amazin… whats your email/contact details so i can you ask you for help when i need to…

  3. Gustavo wrote:

    Thanks for your code! I have a problem: When I run the macro, only convert text to columns in the active sheet, not in all workbook. Do you have any idea? I use excel 2010.

  4. hellyo! wrote:

    Sub importnotepad()
    On Error GoTo errhl
    Dim notepadfile
    notepadfile = Application.GetOpenFilename(“Text Files (*.txt),*.txt”, , “Please select text file…”)
    Workbooks.OpenText Filename:=notepadfile, Origin:=xlWindows, StartRow:=1
    Rows(“1:2”).Delete

    Application.ScreenUpdating = False
    On Error Resume Next
    For Each wksht In ActiveWorkbook.Worksheets
    For Each Row In wksht.Rows
    Columns(col.Column).TextToColumns _
    Destination:=Cells(1, col.Column), _
    DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, _
    Tab:=True, _
    Semicolon:=False, _
    Comma:=True, _
    Space:=False, _
    Other:=False, _
    FieldInfo:=Array(1, 1), _
    TrailingMinusNumbers:=True
    Next Row
    Next wksht
    Cells(1, 1) = “Cols: Deal_Type”
    Cells(1, 2) = “Trade_ID”
    Cells(1, 3) = “Fund_Name”
    Cells(1, 4) = “Curr_Bought”
    Cells(1, 5) = “Amt_Bought”
    Cells(1, 6) = “Curr_Sold”
    Cells(1, 7) = “Amt_Sold”
    Cells(1, 8) = “Spot_Rate”
    Cells(1, 9) = “Fwd_Points”
    Cells(1, 10) = “Swap_Points”
    Cells(1, 11) = “Ex_Rate”
    Cells(1, 12) = “Value_Date”
    Cells(1, 13) = “Status”
    Cells(1, 14) = “Side”
    Cells(1, 15) = “Trade_Date”
    Cells(1, 16) = “Bought_LCU_BaseCCY”
    Cells(1, 17) = “Bought_LCU_CounterCCY”
    Cells(1, 18) = “Bought_LCU_Rate”
    Cells(1, 19) = “Bought_LCU_Points”
    Cells(1, 20) = “Sold_LCU_BaseCCY”
    Cells(1, 21) = “Sold_LCU_CounterCCY”
    Cells(1, 22) = “Sold_LCU_Rate”
    Cells(1, 23) = “Sold_LCU_Points”
    Cells(1, 24) = “Quote Pair”
    Cells(1, 25) = “Comments”

    Application.ScreenUpdating = True

    errhl:
    Exit Sub
    End Sub

  5. Dennic wrote:

    Could anyone let me know how to work out the code to import the fixed-length file to Excel file? I am new for VBA code. :(

  6. Mario wrote:

    Hi
    I hope you can help me.
    I want to spread text to columns, I have the code that makes it, but when I run the code, there is a message box to confirm the replacement, How can I make a default, so the message box doesn not pop up?
    Thanks in advance and I’m pendant of your comments.
    Have a nice day.

  7. Shizue Bright wrote:

    Hey article , I Appreciate the points – Does anyone know if my company would be able to get access to a blank IRS 1099-PATR copy to use ?

  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 […]

Subscribe

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

Translate

English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel


ARCHIVES