Unwanted number format conversion
 

I recently wasted debugging time on a perfectly good macro. The problem
turned out to be Excel: when I manually changed a cell from a value that
looked like a number to another value that looked like a number, Excel
automatically converted the cell's NumberFormat from Text to Number. This
caused a routine that compares cell contents between different worksheets to
fail, because "0399" < 399.

I'm sure most programmers would agree that an application that reassigns
data types without warning the user is arrogant and inconsiderate in the
extreme.

Can I turn this feature off?

Unwanted number format conversion
 

hi,
if the cell(s) is formated to general or number, excel will do this. to
"turn this feature off", format the cell(s) in question to text.

Regards
FSt1

"Allen_N" wrote:

I recently wasted debugging time on a perfectly good macro. The problem
turned out to be Excel: when I manually changed a cell from a value that
looked like a number to another value that looked like a number, Excel
automatically converted the cell's NumberFormat from Text to Number. This
caused a routine that compares cell contents between different worksheets to
fail, because "0399" < 399.

I'm sure most programmers would agree that an application that reassigns
data types without warning the user is arrogant and inconsiderate in the
extreme.

Can I turn this feature off?

Unwanted number format conversion
 

XL's input parser is very consistent in how it treats inputs - it treats
values that look like numbers as numbers unless the cell is preformatted
as Text, or the input is prefixed by an apostrophe (').

Not sure what you see as arrogant...


In article ,
Allen_N wrote:

I recently wasted debugging time on a perfectly good macro. The problem
turned out to be Excel: when I manually changed a cell from a value that
looked like a number to another value that looked like a number, Excel
automatically converted the cell's NumberFormat from Text to Number. This
caused a routine that compares cell contents between different worksheets to
fail, because "0399" < 399.

I'm sure most programmers would agree that an application that reassigns
data types without warning the user is arrogant and inconsiderate in the
extreme.

Can I turn this feature off?

Unwanted number format conversion
 

Hi JE, FSt1...

Okay, please Try this.

Format a cell as text, and enter "03". Then, copy and paste this cell to the
one on the right. Then, in the new cell, change "03" to "04". When I do it,
the format of the cell changes from Number to text and the contents become
"4".

By "arrogant" I mean that it seems as though the Excel programmers want to
coerce our data into the format they think it should be, rather than what we
have explicitly typed it to be. I could be wrong; it may just be a bug.

Thanks for the advice. Please let me know if things look different to you now.

"JE McGimpsey" wrote:

XL's input parser is very consistent in how it treats inputs - it treats
values that look like numbers as numbers unless the cell is preformatted
as Text, or the input is prefixed by an apostrophe (').

Not sure what you see as arrogant...


In article ,
Allen_N wrote:

I recently wasted debugging time on a perfectly good macro. The problem
turned out to be Excel: when I manually changed a cell from a value that
looked like a number to another value that looked like a number, Excel
automatically converted the cell's NumberFormat from Text to Number. This
caused a routine that compares cell contents between different worksheets to
fail, because "0399" < 399.

I'm sure most programmers would agree that an application that reassigns
data types without warning the user is arrogant and inconsiderate in the
extreme.

Can I turn this feature off?

Unwanted number format conversion
 

When I tried it, 04 stayed 04 and the cell was still formatted as text.

You sure you didn't do something else?

Allen_N wrote:

Hi JE, FSt1...

Okay, please Try this.

Format a cell as text, and enter "03". Then, copy and paste this cell to the
one on the right. Then, in the new cell, change "03" to "04". When I do it,
the format of the cell changes from Number to text and the contents become
"4".

By "arrogant" I mean that it seems as though the Excel programmers want to
coerce our data into the format they think it should be, rather than what we
have explicitly typed it to be. I could be wrong; it may just be a bug.

Thanks for the advice. Please let me know if things look different to you now.

"JE McGimpsey" wrote:

XL's input parser is very consistent in how it treats inputs - it treats
values that look like numbers as numbers unless the cell is preformatted
as Text, or the input is prefixed by an apostrophe (').

Not sure what you see as arrogant...


In article ,
Allen_N wrote:

I recently wasted debugging time on a perfectly good macro. The problem
turned out to be Excel: when I manually changed a cell from a value that
looked like a number to another value that looked like a number, Excel
automatically converted the cell's NumberFormat from Text to Number. This
caused a routine that compares cell contents between different worksheets to
fail, because "0399" < 399.

I'm sure most programmers would agree that an application that reassigns
data types without warning the user is arrogant and inconsiderate in the
extreme.

Can I turn this feature off?


--

Dave Peterson

Unwanted number format conversion
 

ps. I used xl2003 to test. But I don't recall ever seeing numberformats change
when I've done something like this.

Allen_N wrote:

Hi JE, FSt1...

Okay, please Try this.

Format a cell as text, and enter "03". Then, copy and paste this cell to the
one on the right. Then, in the new cell, change "03" to "04". When I do it,
the format of the cell changes from Number to text and the contents become
"4".

By "arrogant" I mean that it seems as though the Excel programmers want to
coerce our data into the format they think it should be, rather than what we
have explicitly typed it to be. I could be wrong; it may just be a bug.

Thanks for the advice. Please let me know if things look different to you now.

"JE McGimpsey" wrote:

XL's input parser is very consistent in how it treats inputs - it treats
values that look like numbers as numbers unless the cell is preformatted
as Text, or the input is prefixed by an apostrophe (').

Not sure what you see as arrogant...


In article ,
Allen_N wrote:

I recently wasted debugging time on a perfectly good macro. The problem
turned out to be Excel: when I manually changed a cell from a value that
looked like a number to another value that looked like a number, Excel
automatically converted the cell's NumberFormat from Text to Number. This
caused a routine that compares cell contents between different worksheets to
fail, because "0399" < 399.

I'm sure most programmers would agree that an application that reassigns
data types without warning the user is arrogant and inconsiderate in the
extreme.

Can I turn this feature off?


--

Dave Peterson

Unwanted number format conversion
 

What version of XL are you using?

I tried to reproduce your steps with Mac XL 98, 01, v.X and 2004, and
WinXL 03. Formatted A1 as Text. Entered 03. Copied A1 and pasted into
B1. The Text format was copied to B1. Entered 04 in B1, the format in B1
remained Text in all versions.


In article ,
Allen_N wrote:

Hi JE, FSt1...

Okay, please Try this.

Format a cell as text, and enter "03". Then, copy and paste this cell to the
one on the right. Then, in the new cell, change "03" to "04". When I do it,
the format of the cell changes from Number to text and the contents become
"4".

By "arrogant" I mean that it seems as though the Excel programmers want to
coerce our data into the format they think it should be, rather than what we
have explicitly typed it to be. I could be wrong; it may just be a bug.

Thanks for the advice. Please let me know if things look different to you
now.

Unwanted number format conversion
 

I can't reproduce this behavior in XL2003 or XL97. There must be
something else happening.

Mark Lincoln

On May 28, 2:40 am, Allen_N wrote:
Hi JE, FSt1...

Okay, please Try this.

Format a cell as text, and enter "03". Then, copy and paste this cell to the
one on the right. Then, in the new cell, change "03" to "04". When I do it,
the format of the cell changes from Number to text and the contents become
"4".

By "arrogant" I mean that it seems as though the Excel programmers want to
coerce our data into the format they think it should be, rather than what we
have explicitly typed it to be. I could be wrong; it may just be a bug.

Thanks for the advice. Please let me know if things look different to you now.



"JE McGimpsey" wrote:
XL's input parser is very consistent in how it treats inputs - it treats
values that look like numbers as numbers unless the cell is preformatted
as Text, or the input is prefixed by an apostrophe (').

Not sure what you see as arrogant...

In article ,
Allen_N wrote:

I recently wasted debugging time on a perfectly good macro. The problem
turned out to be Excel: when I manually changed a cell from a value that
looked like a number to another value that looked like a number, Excel
automatically converted the cell's NumberFormat from Text to Number. This
caused a routine that compares cell contents between different worksheets to
fail, because "0399" < 399.

I'm sure most programmers would agree that an application that reassigns
data types without warning the user is arrogant and inconsiderate in the
extreme.

Can I turn this feature off?- Hide quoted text -

- Show quoted text -

Unwanted number format conversion
 

Thanks Mark, Dave, JE...

I'm using XL2003. I'll do some more testing and get back to you. This
seems pretty weird!


"Mark Lincoln" wrote:

I can't reproduce this behavior in XL2003 or XL97. There must be
something else happening.

Mark Lincoln

On May 28, 2:40 am, Allen_N wrote:
Hi JE, FSt1...

Okay, please Try this.

Format a cell as text, and enter "03". Then, copy and paste this cell to the
one on the right. Then, in the new cell, change "03" to "04". When I do it,
the format of the cell changes from Number to text and the contents become
"4".

By "arrogant" I mean that it seems as though the Excel programmers want to
coerce our data into the format they think it should be, rather than what we
have explicitly typed it to be. I could be wrong; it may just be a bug.

Thanks for the advice. Please let me know if things look different to you now.



"JE McGimpsey" wrote:
XL's input parser is very consistent in how it treats inputs - it treats
values that look like numbers as numbers unless the cell is preformatted
as Text, or the input is prefixed by an apostrophe (').

Not sure what you see as arrogant...

In article ,
Allen_N wrote:

I recently wasted debugging time on a perfectly good macro. The problem
turned out to be Excel: when I manually changed a cell from a value that
looked like a number to another value that looked like a number, Excel
automatically converted the cell's NumberFormat from Text to Number. This
caused a routine that compares cell contents between different worksheets to
fail, because "0399" < 399.

I'm sure most programmers would agree that an application that reassigns
data types without warning the user is arrogant and inconsiderate in the
extreme.

Can I turn this feature off?- Hide quoted text -

- Show quoted text -

Unwanted number format conversion
 

Hi gang,

I've mostly worked out what is going on, here.

In my example, the cells in question contain values like "0390", which are
codes for our company's branches. These come into the spreadsheet (along
with other data) via an MS Query from a database on a server. The branch
code field in the source table is a string of length 4.

So, a branch code goes into a worksheet cell, via MS Query, with the leading
"0" intact. But, because the destinatioin cell has the default format
'General', editing the cell causes it to be reinterpreted as a 'Number', and
the zero gets chopped.

I guess I now have less reason to be peeved about this behaviour, but it
would be nice if Excel would try to preserve or mimic the data types of
fields in external data sources. Is there, in fact, a way to make this
happen?

Thanks for bearing with me!

Unwanted number format conversion
 

I see there hasn't been any answer in over a week. If you haven't
already done so, you may want to post this as a new question. If you
have, reply here and I'll look for the new thread.

I have never imported data via MS Query, so I'm just throwing this out
as a suggestion.

If you know which columns will contain numbers with leading zeros that
must remain intact, you could format those cells as Text before
importing your data. You could also try formatting the cells to Text
after importing your data.

You might be able to change the cell formatting "on the fly" during
the import process. This would have the advantage being automatic
during importing instead of requiring an extra step either before or
after which can easily be omitted.

Mark Lincoln

On May 29, 11:24 pm, Allen_N wrote:
Hi gang,

I've mostly worked out what is going on, here.

In my example, the cells in question contain values like "0390", which are
codes for our company's branches. These come into the spreadsheet (along
with other data) via an MS Query from a database on a server. The branch
code field in the source table is a string of length 4.

So, a branch code goes into a worksheet cell, via MS Query, with the leading
"0" intact. But, because the destinatioin cell has the default format
'General', editing the cell causes it to be reinterpreted as a 'Number', and
the zero gets chopped.

I guess I now have less reason to be peeved about this behaviour, but it
would be nice if Excel would try to preserve or mimic the data types of
fields in external data sources. Is there, in fact, a way to make this
happen?

Thanks for bearing with me!