cell formatting and format conversion

  • Thread starter Thread starter adimar
  • Start date Start date
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.
 
Yes, this works fine.

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

Thank you.
 
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.
 
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.
 
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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

conditional format fails when copied 1
How to Keep Excel Sheet Formatting On a CSV Sheet with vba 1
conditional format - table - cell empty return default color 1
Text Formats 4
Copy excel cell to webform (search box) and press enter 10
Date formats 3
Using functions in string formatted cells 4
Conditional Formatting with VBA 4

Back
Top