Formatting 11001981003213 as text..

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
Click to expand...

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
 
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
Click to expand...

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


Ron Rosenfeld said:
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
Click to expand...
 
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], 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.
 
Back
Top