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.