Auto Date Conversion Not Requested

[Guest]

When opening a .csv file or pasting in a column of number with the format of "01-5308", Excel autoformats to "Jan-08"...then when converting the format of the column to text, Excel translates to the date range numeric of "1244748". Nifty feature for corrupting data accuracy.

How do I turn off such a nifty feature?

 

[Ragdyer]

Pre-format the column as Text.
Formatting to text after the data is already present usually doesn't work.

An alternative when entering (keying in) this type of data is to precede the
data with an apostrophe ('), which is not visible in the cell itself, but
can be seen in the formula bar.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

When opening a .csv file or pasting in a column of number with the format

of "01-5308", Excel autoformats to "Jan-08"...then when converting the
format of the column to text, Excel translates to the date range numeric of
"1244748". Nifty feature for corrupting data accuracy.

 

[Guest]

Thank you for the reply, however, it appears that when opening files and/or pasting data the "pre-formatting" to text gets reset to the nifty conversion feature.

To date, I have been pasting to a notepad, then to Excel with limited luck.

 

[RagDyeR]

Have you tried "PasteSpecial", then select "Text".

This should retain (protect) the *pre-formatted* integrity of the column.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Thank you for the reply, however, it appears that when opening files and/or
pasting data the "pre-formatting" to text gets reset to the nifty conversion
feature.

To date, I have been pasting to a notepad, then to Excel with limited luck.

 

[Guest]

Thank you for the reply. Unfortunately, when pasting special, the data now must be parsed into columns (adding four more steps).

In addition, when opening a .csv file with the data 01-5088 in a column - the auto conversion feature to a date occurs.

How does one turn off such features?

 

[Ragdyer]

After pre-formatting your XL columns to text, delete the .csv extension
before importing and/or opening this new file.
XL will then not recognize it, and should open the Import Wizard, where you
can then stipulate Text.

AFAIK, there is no other way to prevent XL from converting these "numbers",
except to make them "non-numbers", namely, TEXT.

When you say that this forces you into four extra parsing steps, why not
post here exactly what you need to accomplish, and perhaps there might be an
easy and simple "TextToColumns" solution to your problem?
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Thank you for the reply. Unfortunately, when pasting special, the data

now must be parsed into columns (adding four more steps).

In addition, when opening a .csv file with the data 01-5088 in a column -

the auto conversion feature to a date occurs.

 

[Ikkeboeken]

I have an issue where my website host provides me with a batch database file
each day. In order to make the forms easier for the customer the dates asked
in the forms are split into 3 list menus, with seperate day, month and year
fields that all use the same name. The field values for all three are then
concatentated into a single date field on the server with each field
seperated by a comma. I receive a tab delimited text file each day with the
contents. When I run the text import wizard I select all fields as text and
according to my worksheet settings, all of the fields are text. However,
when I run find & replace to remove the commas, Excell automatically
concerts the results into date format. This is useless for me as I need to
export the data to another program, as text, and print the data as text...
WYSIWYG. How can this be either turned off, or accomplished in another way?

Thank you in advance.

 

[RagDyeR]

I don't quite follow exactly what you're saying.

Do you mean that after the import into XL, you have this data in individual
cells:

2,4,03
1,20,02
4,16,04
.... etc.
AND, this data is all formatted as Text?

You say you now want to replace the commas?

With what?
Do you want slashes (4/16/04)?
Or what?

If you do want to change
4,16,04
to
4/16/04
AND ... not have the 4/16/04 in "date format", you can try using a helper
column to transform your data.

With data starting in A1, enter this in B1:

=SUBSTITUTE(A1,",","/")

And drag down to copy as needed.

Now, this should give you the "display" that you want.
You now have to *remove* the conversion formula, leaving the displayed data
behind.

While the "new" column B is still selected, right click in the selection and
choose "Copy".
Right click again, and choose "PasteSpecial", and click on "Values", then
<OK>.
NOW, right click again, for the third time and choose "FormatCells".
Click on "Text", then <OK>.

And, if I guessed correctly as to what you're looking for, you should now
have a column of "Text Formatted" data ready to export to your other
program.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================




I have an issue where my website host provides me with a batch database file
each day. In order to make the forms easier for the customer the dates asked
in the forms are split into 3 list menus, with seperate day, month and year
fields that all use the same name. The field values for all three are then
concatentated into a single date field on the server with each field
seperated by a comma. I receive a tab delimited text file each day with the
contents. When I run the text import wizard I select all fields as text and
according to my worksheet settings, all of the fields are text. However,
when I run find & replace to remove the commas, Excell automatically
concerts the results into date format. This is useless for me as I need to
export the data to another program, as text, and print the data as text...
WYSIWYG. How can this be either turned off, or accomplished in another way?

Thank you in advance.