Since you indicated VBA....
When using VBA to implement Text-to-columns,
you have the option to indicate the data type of each field being parsed.
Sample data in A1:A7
00123456789-FFF
00123456788-FFF
00123456787
00123456777-FFF
00123456999-FFF
00123455555-FFF
00123456444
Where:
Field 1 is text
Field 2 is anything, actually
Sub ParseMyText()
[A1:A800].TextToColumns _
Destination:=Range("A1"), _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 2), Array(11, 9))
End Sub
The FieldInfo parameter, which is implemented this way, determines the data type:
FieldInfo:=Array(Array(0, 2), Array(11, 9))
For fixed-width source data,
the first argument of each array identifies a field by position (beginning with location 0)
and
the second argument of each array indicates the data type
Value Name Description
1 xlGeneralFormat General
2 xlTextFormat Text
3 xlMDYFormat MDY date format
4 xlDMYFormat DMY date format
5 xlYMDFormat YMD date format
6 xlMYDFormat MYD date format
7 xlDYMFormat DYM date format
8 xlYDMFormat YDM date format
9 xlSkipColumn Column is not parsed
10 xlEMDFormat EMD date format
Bookmarks