Cell Format

Thanks again.
Any idea what this one is doing and how to fix it?

I insert 90014E04 and Excel converts it to a scientific
number 9.00E+08

Any idea what the formula would be to convert this back?






-----Original Message-----
Hi CC!

You can use a helper column with the formula:

=TEXT(MONTH(A1),"00")&"-"&TEXT(YEAR(A1),"0000")

Once you have the text entries you can

Copy
Edit Paste Special Values OK

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax
and Arguments)
available free to good homes.
"CC" wrote in
message
...
Thanks, I understand that it is interpreting 30-2040 as
a
date and how to correct this for newly created
spreadsheets.

The issue that I am now faced with is lots of Excel
spreadsheets with tons cells that are incorrectly
formatted. As you know changing the format does not
recover the correct data.

Could this be an Excel version issue? My understanding
is
that the person who had originally created this
spreadsheet was seeing 03-2040 not the date format.
Unfortunatly I do not know the deatils of their setup.





-----Original Message-----
Hi CC!

The entry of 03-2040 is being interpreted as 1-Mar-2040

You need to pre-format the cells as text.

I don't think that you can fix the format after entry
but
you can
parse these rogue entries:

=TEXT(MONTH(A1),"00")&"-"&YEAR(A1)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax
and Arguments)
available free to good homes.
"CC" wrote in
message
...
When I type 03-2040 in a cell it automatically
reformats
the text to 51196. With some other number
combinations
it
reformats to a date. Is there any way to turn off
Auto
Formatting and how can I fix the format of existing
data.

Hi
the value 90014E04 is a valid number for Excel. Two possible
workarounds (if this should be a text):
- format the cell as 'Text' prior to entering
- preced the value with an apostropeh: '90014E04

--
Regards
Frank Kabel
Frankfurt, Germany
schrieb im Newsbeitrag
...
Thanks again.
Any idea what this one is doing and how to fix it?

I insert 90014E04 and Excel converts it to a scientific
number 9.00E+08

Any idea what the formula would be to convert this back?






-----Original Message-----
Hi CC!

You can use a helper column with the formula:

=TEXT(MONTH(A1),"00")&"-"&TEXT(YEAR(A1),"0000")

Once you have the text entries you can

Copy
Edit Paste Special Values OK

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax
and Arguments)
available free to good homes.
"CC" wrote in
message
...
Thanks, I understand that it is interpreting 30-2040 as
a
date and how to correct this for newly created
spreadsheets.

The issue that I am now faced with is lots of Excel
spreadsheets with tons cells that are incorrectly
formatted. As you know changing the format does not
recover the correct data.

Could this be an Excel version issue? My understanding
is
that the person who had originally created this
spreadsheet was seeing 03-2040 not the date format.
Unfortunatly I do not know the deatils of their setup.





-----Original Message-----
Hi CC!

The entry of 03-2040 is being interpreted as 1-Mar-2040

You need to pre-format the cells as text.

I don't think that you can fix the format after entry
but
you can
parse these rogue entries:

=TEXT(MONTH(A1),"00")&"-"&YEAR(A1)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax
and Arguments)
available free to good homes.
"CC" wrote in
message
...
When I type 03-2040 in a cell it automatically
reformats
the text to 51196. With some other number
combinations
it
reformats to a date. Is there any way to turn off
Auto
Formatting and how can I fix the format of existing
data.