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

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

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

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

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

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