Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import TXT file into Excel and keep first "0" on numbers
Hi,
I am importing a text file into Excel but any numbers that start with "0" lose the "0" during the import. For example 0123 becomes 123. It is vital that I can keep the "0", is there anyway to resolve this issue? Thanks, Brett |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import TXT file into Excel and keep first "0" on numbers
The code I am using to import the text file is as follows:
Workbooks.OpenText Filename:=FILEtoOPEN, Origin:= _ xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier _ :=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, _ FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array _ (5, 4), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array( _ 11, 1), Array(12, 1), Array(13, 1)) Range("A1:AZ1500").Select Selection.Copy |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import TXT file into Excel and keep first "0" on numbers
I don't know which field is the one you are asking about, but you need to
import that field as text. Most of your fields are being imported as the 'general' format, which assumes that anything that looks like a number is a number. Each field is denoted in the macro by something like Array(x, y), where x is the number of the field (first field is 1, second field is 2, etc.) and y is what kind of data it is. Most of your fields are Array(x, 1) which is the general format. Find the field you are concerned about and change the data type to 2, for text. For example, if the field you are concerned about is the third field, you would change Array(3, 1) to Array(3, 2). Then it should import properly. Hope this helps, Hutch " wrote: The code I am using to import the text file is as follows: Workbooks.OpenText Filename:=FILEtoOPEN, Origin:= _ xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier _ :=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, _ FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array _ (5, 4), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array( _ 11, 1), Array(12, 1), Array(13, 1)) Range("A1:AZ1500").Select Selection.Copy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create an "import-friendly" text-file from excel? | Excel Discussion (Misc queries) | |||
Lost "File Menu" - now it's "Edit / View / Insert.." but no "F | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Excel: How to import multiple XML "Repeating child elements" same. | Excel Worksheet Functions | |||
Utility to "clean up" or "defrag" large Excel file | Excel Discussion (Misc queries) |