Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting 11001981003213 as text..
11001981003213 is a serial number. I'm an Access Programmer running
automation to Excel. This long serial number, even when formatted as text on the Auto_Open event, displays as scientific notation. Is there an Excel setting to ensure it appears as text? TIA - Bob |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting 11001981003213 as text..
On Thu, 15 Nov 2007 20:03:01 -0800, Bob Barnes
wrote: 11001981003213 is a serial number. I'm an Access Programmer running automation to Excel. This long serial number, even when formatted as text on the Auto_Open event, displays as scientific notation. Is there an Excel setting to ensure it appears as text? TIA - Bob Either format the cell as text BEFORE entering the number; or precede the number with a single quote (') The single quote will not appear in the cell or on any printout (although you will see it in the formula bar). --ron |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting 11001981003213 as text..
On Thu, 15 Nov 2007 20:03:01 -0800, Bob Barnes
wrote: is a serial number. I'm an Access Programmer running automation to Excel. This long serial number, even when formatted as text on the Auto_Open event, displays as scientific notation. Is there an Excel setting to ensure it appears as text? TIA - Bob Oh, also, if all the serial numbers are less than 16 digits, you could also just format the cell as number (with zero decimal places). In that case, though, any leading zero's will be dropped. --ron |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting 11001981003213 as text..
Ron..This was the best solution seeing the string can get longer than 16
characters.. "Either format the cell as text BEFORE entering the number". Thank you - Bob "Ron Rosenfeld" wrote: On Thu, 15 Nov 2007 20:03:01 -0800, Bob Barnes wrote: 11001981003213 is a serial number. I'm an Access Programmer running automation to Excel. This long serial number, even when formatted as text on the Auto_Open event, displays as scientific notation. Is there an Excel setting to ensure it appears as text? TIA - Bob Either format the cell as text BEFORE entering the number; or precede the number with a single quote (') The single quote will not appear in the cell or on any printout (although you will see it in the formula bar). --ron |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting 11001981003213 as text..
Ron - Thank you - Bob
"Ron Rosenfeld" wrote: On Thu, 15 Nov 2007 20:03:01 -0800, Bob Barnes wrote: is a serial number. I'm an Access Programmer running automation to Excel. This long serial number, even when formatted as text on the Auto_Open event, displays as scientific notation. Is there an Excel setting to ensure it appears as text? TIA - Bob Oh, also, if all the serial numbers are less than 16 digits, you could also just format the cell as number (with zero decimal places). In that case, though, any leading zero's will be dropped. --ron |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting 11001981003213 as text..
Suggestions by responders suffice in trivial cases, but no one has suggested
a general purpose solution, for example when scientific notation is intermittently applied on a column containing thousands of rows. Highlighting the entire column and formatting it as Text does not work. Manually adding apostrophes where required does appear to work, but would take many hours for each column. My attempt at a general purpose solution is to use a contenate formula as follows: Cell A1: TEXT "273074206023", displayed as "2.73074E+11" Cell A2: =CONCATENATE("'",a1) This does not work. The leading apostrophe displays in the cell. Next attempt: Cell A1: TEXT "273074206023", displayed as "2.73074E+11" Cell A2: =Trim(CONCATENATE(" ",a1)) This WORKS. Furthermore, if we copy/paste special/Value back to the original cell (using the legacy key sequence [ALT][S], as I know of no other way to reach this feature in Excel 2007, the desire formatting is retained (that is, the scientific notation is gone), even though the contents and formatting are visually indistinguishable from the original. If someone can tell me why the last method appears to work, and how to circumvent the auto-formatting by a more straightforward means, I will be nudged a little bit closer to recommending an upgrade to Excel 2007 for several thousand current users of Excel 2003. Cheers. -- haile "Bob Barnes" wrote: 11001981003213 is a serial number. I'm an Access Programmer running automation to Excel. This long serial number, even when formatted as text on the Auto_Open event, displays as scientific notation. Is there an Excel setting to ensure it appears as text? TIA - Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Text String: Formatting Text and Numbers? | Excel Discussion (Misc queries) | |||
Conditional Formatting based on text within a cell w/ text AND num | Excel Worksheet Functions | |||
Text formatting | Excel Worksheet Functions | |||
Conditional Formatting based on Text within Text | Excel Discussion (Misc queries) | |||
Formatting text.... | Excel Worksheet Functions |