Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel format conversion | New Users to Excel | |||
Time format conversion | Excel Discussion (Misc queries) | |||
date format conversion | Excel Programming | |||
Conversion of date into different format | Excel Discussion (Misc queries) | |||
Date Format - Conversion | Excel Discussion (Misc queries) |