Rabu, 15 Oktober 2025

VBA Text to Column solution with functionable

Text to column VBA to generate general type to text with function. following this script :

1. Create Function
2. Call the function
3. Function detail

---1. Create function----


Function TextToColumn(Reng As Range, Col As Integer, tipe As Variant)

Reng.TextToColumns Destination:=Reng, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(Col, tipe), _
TrailingMinusNumbers:=True

End Function

---2. Call the function----
Worksheets("Sheet1").Select
Call TextToColumn(ActiveWorkbook.Sheets("Sheet1").Range("DD:DD"), 108, xlTextFormat)

'Reng = ActiveWorkbook.Sheets("Sheet1").Range("DD:DD") So, the range is columns DD
'Index Columns = 108 as counting column A = 1 so Column DD is 108
'Tipe = xlTextFormat

'example for other type such as xlGeneralFormat or following this reference table :
'xlDMYFormat 4 DMY date format.
'xlDYMFormat 7 DYM date format.
'xlEMDFormat 10 EMD date format.
'xlGeneralFormat 1 General.
'xlMDYFormat 3 MDY date format.
'xlMYDFormat 6 MYD date format.
'xlSkipColumn 9 Column is not parsed.
'xlTextFormat 2 Text.
'xlYDMFormat 8 YDM date format.
'xlYMDFormat 5 YMD date format.
'if you want the type is date you can choose xlDMYFormat to change xlTextFormat

'it is work well for my worksheet.