Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12 _
, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), _
Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array( _
25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), _
Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array( _
38, 1), Array(39, 1), Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), _
Array(45, 1), Array(46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1), Array( _
51, 1), Array(52, 1), Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57, 1), _
Array(58, 1), Array(59, 1), Array(60, 1), Array(61, 1), Array(62, 1), Array(63, 1), Array( _
64, 1), Array(65, 1)), TrailingMinusNumbers:=True
Dim myArrB() As Variant
Dim iItem As Long
Dim myArrayTemp() As Variant
Dim myFields As Long
myFields = 2
myFields = myFields - 1 ' Zero Based
' Create a Jagged Array that is required by TextToColumns
' -------------------------------------------------------
ReDim myArrB(0 To myFields)
For iItem = 0 To myFields
ReDim myArrayTemp(0 To 1)
myArrayTemp(0) = iItem + 1
myArrayTemp(1) = 1 ' xlGeneralFormat DataType
myArrB(iItem) = myArrayTemp
Next iItem
' -------------------------------------------------------
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=myArrB, TrailingMinusNumbers:=True
Dim myArrB() As Variant ' Array to be used for FieldInfo
Dim iItem As Long ' Counter for Fields in FieldInfo
Dim myArrayTemp() As Variant ' Temporary Array used for Arrays inside of FieldInfo Array
Dim myFields As Long ' Variable to hold number of fields we want to setup
myFields = 3 ' Number of fields we want.
myFields = myFields - 1 ' Zero Based - This is for the array index. subtract 1
' Create a Jagged Array that is required by TextToColumns
' -------------------------------------------------------
ReDim myArrB(0 To myFields) ' Redimension the Array to the number of fields
For iItem = 0 To myFields ' Loop through each field
ReDim myArrayTemp(0 To 1) ' Redimension the Array inside the FieldInfo Array
myArrayTemp(0) = iItem + 1 ' iItem is the field number
myArrayTemp(1) = 2 ' xlTextFormat DataType
myArrB(iItem) = myArrayTemp ' Build the FileInfo Array by storing the Array inside the Array
Next iItem
' -------------------------------------------------------
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=myArrB, TrailingMinusNumbers:=True
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
11 Experts available now in Live!