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 |
[…] a bit of VBA code to convert Text to Column which will work across multiple columns selected together at one […]
September 21st, 2009 at 1:18 am