How do I retain the format of varying numbers in Excel?

[Guest]

I am importing a pipe delimited text file into Excel 2003.

One column contains numbers that may be formatted in various ways. A number
may contain a combinations of a colon and a decimal. Or, it may contain only
a colon. A number may have a zero at the beginning or end of the number.

28:08.92
92:00
08:40

Excel automatically changes the formatting of this column and produces
incorrect results. It strips zeros, rounds up, displays as "time", etc.
Examples:

Text File Displays.....Excel Changes To
"08:12.07"..............08:12.1
"04:00"..................4:00
"92:00"..................92:00:00

Attempting to manually format this column after data has been imported is
not successful. I have been unable to define a format that covers the
various formats that may exist.

How do I import this pipe delimited text file into an Excel worksheet and
maintain the correct format of the numbers shown above?

Thanks!

 

[Guest]

Import the fields using a data type of text for each field for which you want
to retain the format, (not as 'General' which is the default). When General
is used, you leave it up to Excel to decide what the data type is, and if the
data looks like a date or time to Excel that is what you get.

 

[Guest]

I changed the entire page to format "text", then imported the file. No
change. Number formats still incorrect.

 

[Guest]

I put you data into two files:
x.csv
and
x.txt

I tried to open the .csv file, it Excel made the un-wanted conversions.
From Excel, I opened the .txt file. I told the Wizard that the datat was
delimited, told it to use the tab, and told the Wizard to use text in the
column.

The Wizard performed the import without conversion.

 

[Guest]

If you feel comfortable with VBA, here is the recorded code:


Sub Macro1()
Workbooks.OpenText Filename:= _
"C:\x.txt", Origin:=437, StartRow:= _
1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(1, 2), _
TrailingMinusNumbers:=True
End Sub

 

[Guest]

You need to use the import data wizard to specify the data types to be
applied upon the data import. I was presuming that you were using the import
wizard since you said you were using pipe delimited text.

The wizard can be activated with the menu bar using the Data / Import
External Data / Import Data option, and change the file type and path to
locate your data file. Specify that you want a delimited file in step 1,
specify the delimiter in Step 2, and in Step 3 specify the data type as text
for each of the fields.

HTH,

TK

 

[Guest]

Thanks! All resolved! I appreciate the help (from both of you)!