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 |
Prevent Users Ctrl Break (ing) Your VBA Code During Execution 
Multithreaded VBA – An Approach To Processing Using VBScript 
VBA IF Function – Using IF, ELSE:, ELSEIF, IF THEN in VBA code 
Treemap in Excel – Coming Soon 
VBA Select Case – Using VBA Select Case Statement in Excel 
Control Chart in Excel Using VBA – Six Sigma Control Chart Code & Software 
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.
August 10th, 2012 at 1:03 pmKaren
You are amazin… whats your email/contact details so i can you ask you for help when i need to…
September 11th, 2012 at 5:15 amThanks 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.
February 27th, 2013 at 11:27 amSub 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:
April 9th, 2013 at 6:34 amExit Sub
End Sub
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.
July 1st, 2013 at 4:18 amHi
November 30th, 2015 at 1:20 pmI 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.
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 ?
March 31st, 2016 at 12:51 pm