cell formatting and format conversion
 

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.

cell formatting and format conversion
 

Hi,

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

Mike

"adimar" wrote:


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: 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.

cell formatting and format conversion
 

Yes, this works fine.

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

Thank you.


"Mike H" wrote:

Hi,

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

Mike

"adimar" wrote:


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: 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.

cell formatting and format conversion
 

Hi,

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

Mike

"adimar" wrote:


Yes, this works fine.

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

Thank you.


"Mike H" wrote:

Hi,

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

Mike

"adimar" wrote:


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: 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.

cell formatting and format conversion
 

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.


"Mike H" wrote:

Hi,

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

Mike

"adimar" wrote:


Yes, this works fine.

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

Thank you.


"Mike H" wrote:

Hi,

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

Mike

"adimar" wrote:


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: 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.

cell formatting and format conversion
 

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.

"adimar" wrote:


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.


"Mike H" wrote:

Hi,

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

Mike

"adimar" wrote:


Yes, this works fine.

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

Thank you.


"Mike H" wrote:

Hi,

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

Mike

"adimar" wrote:


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: 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.

cell formatting and format conversion
 

NumberFormat ="@" works fine.

Thank you.

"JLGWhiz" wrote:

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.

"adimar" wrote:


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.


"Mike H" wrote:

Hi,

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

Mike

"adimar" wrote:


Yes, this works fine.

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

Thank you.


"Mike H" wrote:

Hi,

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

Mike

"adimar" wrote:


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: 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.