I'm using TextToColumns in a macro to convert several huge text files
to a spreadsheet. Some of the raw data is numbers but I need everything
to be copied as text. I found this question numerous times in this
forum but none with the answer I'm looking for.
The problem is, even with the code below the numbers are wrong.
For example, there is a difference between "2" and "02" in my data,
but even with the NumberFormat command the text both variations
will copy as "2", and some numeric strings are converted to SciNote.
Is there a way to copy all text from the raw document as text
without any translation at all?

' Force number format to text:
Cells.Select
Selection.NumberFormat = "@"
' Convert unformatted text to 15 columns:
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(5, 1), Array(11, 1), Array(17,
1), . . . . .

I'm using Excel 2003, with XP Pro.

Mike Henry

The problem is that all your codes are 1.

Replace:

Array(0, 1), Array(5, 1), Array(11, 1),.........

with:

Array(0, 2), Array(5, 2), Array(11, 2),..........

--
Gary''s Student - gsnu200775


"Mike H" wrote:

I'm using TextToColumns in a macro to convert several huge text files
to a spreadsheet. Some of the raw data is numbers but I need everything
to be copied as text. I found this question numerous times in this
forum but none with the answer I'm looking for.
The problem is, even with the code below the numbers are wrong.
For example, there is a difference between "2" and "02" in my data,
but even with the NumberFormat command the text both variations
will copy as "2", and some numeric strings are converted to SciNote.
Is there a way to copy all text from the raw document as text
without any translation at all?

' Force number format to text:
Cells.Select
Selection.NumberFormat = "@"
' Convert unformatted text to 15 columns:
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(5, 1), Array(11, 1), Array(17,
1), . . . . .

I'm using Excel 2003, with XP Pro.

Mike Henry

Ok, thanks.
That works but the description in the help file sounds like
"0" is for general
"1" is for text
"2" is for a date format.
I guess not.

M.H.
-------------------------------
"Gary''s Student" wrote:
The problem is that all your codes are 1.
Replace:
Array(0, 1), Array(5, 1), Array(11, 1),.........
with:
Array(0, 2), Array(5, 2), Array(11, 2),..........
--
Gary''s Student - gsnu200775