cell formatting and format conversion

A

adimar

I have a question regarding Excel's interpretation of entered info and how
it converts it.

Example 1: I type in the following in a cell: 074410E4
Excel displays it as 7.44E+08

Example 2: I type in the following in a cell: 08121126
Excel displays it as 8121016

I tried setting different formatting for the cells and copying around and
still cannot get the cell to display exactly how I entered it. Same undesired
behavior when copying/pasting with a macro.

My goal is to have the data exactly the way I entered it.


Thank you.
 
M

Mike H

Hi,

Format as text or type an apostrophe ' first. It wont show in the cell.

Mike
 
A

adimar

Yes, this works fine.

I could prepend with ' in the macro too. Is there another way to stop Excel
making assumptions?

Thank you.
 
M

Mike H

Hi,

That would depend which assumption you wanted to challenge. Specifically?

Mike
 
A

adimar

I find the number conversions work differently than other types.

If I format the cell as text and paste 074410E4 Excel converts it to
scientific format, overriding the text format previoulsy set.

That's not the case with a cell formatted as text: when I type in 10/1/08 -
it stays as typed and Excel provides info (marker in top left corner) letting
the user to decide the final format.

Also, I can use datevalue() to convert to date, or text(); is there
something similar for numbers, scientific format in this case?

Thank you.
 
J

JLGWhiz

Not really sure what you are after, but look at NumberFormat property in VBA
help files and the Type conversion functions. These are useful in many cases
to get the proper values returned for variables and for converting existing
values from worksheet cells to data types required to make VBA execute
correctly.
 
A

adimar

NumberFormat ="@" works fine.

Thank you.

JLGWhiz said:
Not really sure what you are after, but look at NumberFormat property in VBA
help files and the Type conversion functions. These are useful in many cases
to get the proper values returned for variables and for converting existing
values from worksheet cells to data types required to make VBA execute
correctly.
Click to expand...
 
Top