We're seeing a problem in Excel that I've never come across. All of the
machines that we're seeing this on just downloaded this month's auto-update,
but I haven't looked yet to see if there were any Office fixes. I'm
wondering if any of you are seeing this or might have a solution for me.

We typically work with 16-digit credit card-style numbers, where the entire
number is treated as text. Typically, trying to format the number as text
results in scientific notation, so I usually leave it as a number with no
commas or decimals. However, no amount of formatting seems to affect this
issue.

When I enter a number in to a cell, if it is longer then 15 digits, any
number after 15 changes to a zero. You can see in the example I've pasted
below where I was typing in all 4's ending with a 3, from 2 digits to 17
digits long. Even if you go to edit the data, the last numbers have been
changed to a 0, it's not just how the formatting is showing the number.

43
443
4443
44443
444443
4444443
44444443
444444443
4444444443
44444444443
444444444443
4444444444443
44444444444443
444444444444443
4444444444444440
44444444444444400

Am I missing something? Help!

Thanks!!
beth

Hi Beth:

This is not a new problem, it is an old limitation. Integer numbers can
have 15 digits. If you need more than 15, just precede the value by a single
quote (apostrophe) or format the cell as Text.
--
Gary's Student


"BethP" wrote:

We're seeing a problem in Excel that I've never come across. All of the
machines that we're seeing this on just downloaded this month's auto-update,
but I haven't looked yet to see if there were any Office fixes. I'm
wondering if any of you are seeing this or might have a solution for me.

We typically work with 16-digit credit card-style numbers, where the entire
number is treated as text. Typically, trying to format the number as text
results in scientific notation, so I usually leave it as a number with no
commas or decimals. However, no amount of formatting seems to affect this
issue.

When I enter a number in to a cell, if it is longer then 15 digits, any
number after 15 changes to a zero. You can see in the example I've pasted
below where I was typing in all 4's ending with a 3, from 2 digits to 17
digits long. Even if you go to edit the data, the last numbers have been
changed to a 0, it's not just how the formatting is showing the number.

43
443
4443
44443
444443
4444443
44444443
444444443
4444444443
44444444443
444444444443
4444444444443
44444444444443
444444444444443
4444444444444440
44444444444444400

Am I missing something? Help!

Thanks!!
beth

Thanks for the quick reply. It's so weird that I've never run in to this
before!

If you pull in a list of data with 16-digit numbers, and perform a cell
format to set it all to text, by default it converts to scientific formula.
Is there a way to keep this from happening without having to concatenate
every line with an apostrophe? (We occasionally pull 1000 row x 30 column
spreadsheets with this kind of data, and it's often by techs who are not all
that Excel savvy.)

Thanks again,
beth

"Gary''s Student" wrote:

Hi Beth:

This is not a new problem, it is an old limitation. Integer numbers can
have 15 digits. If you need more than 15, just precede the value by a single
quote (apostrophe) or format the cell as Text.
--
Gary's Student


"BethP" wrote:

We're seeing a problem in Excel that I've never come across. All of the
machines that we're seeing this on just downloaded this month's auto-update,
but I haven't looked yet to see if there were any Office fixes. I'm
wondering if any of you are seeing this or might have a solution for me.

We typically work with 16-digit credit card-style numbers, where the entire
number is treated as text. Typically, trying to format the number as text
results in scientific notation, so I usually leave it as a number with no
commas or decimals. However, no amount of formatting seems to affect this
issue.

When I enter a number in to a cell, if it is longer then 15 digits, any
number after 15 changes to a zero. You can see in the example I've pasted
below where I was typing in all 4's ending with a 3, from 2 digits to 17
digits long. Even if you go to edit the data, the last numbers have been
changed to a 0, it's not just how the formatting is showing the number.

43
443
4443
44443
444443
4444443
44444443
444444443
4444444443
44444444443
444444444443
4444444444443
44444444444443
444444444444443
4444444444444440
44444444444444400

Am I missing something? Help!

Thanks!!
beth

Hi


"BethP" wrote in message
...
Thanks for the quick reply. It's so weird that I've never run in to this
before!

If you pull in a list of data with 16-digit numbers, and perform a cell
format to set it all to text, by default it converts to scientific
formula.
Is there a way to keep this from happening without having to concatenate
every line with an apostrophe? (We occasionally pull 1000 row x 30 column
spreadsheets with this kind of data, and it's often by techs who are not
all
that Excel savvy.)


Format the range with original numbers as text.
Into some free column, enter the formula like (it's assumed your numbers are
in column A, when otherwise, adjust the formula)
="" & A2
for cell p.e. X2,
and copy down for entire table.
Select whole range with formulas, and copy it. PasteSpecial it as Values to
original values range.
Delete the helper column.


Arvi Laanemets

Hi Beth:

Arvi's approach is very good. Another trick is to control things when the
data gets pulled in.

If possible, get the data as a text file with a .txt type rather than .csv
When you open a .txt file in Excel, the Import Wizard will be invoked. You
can tell the Wizard to treat that field as text.
--
Gary's Student


"BethP" wrote:

Thanks for the quick reply. It's so weird that I've never run in to this
before!

If you pull in a list of data with 16-digit numbers, and perform a cell
format to set it all to text, by default it converts to scientific formula.
Is there a way to keep this from happening without having to concatenate
every line with an apostrophe? (We occasionally pull 1000 row x 30 column
spreadsheets with this kind of data, and it's often by techs who are not all
that Excel savvy.)

Thanks again,
beth

"Gary''s Student" wrote:

Hi Beth:

This is not a new problem, it is an old limitation. Integer numbers can
have 15 digits. If you need more than 15, just precede the value by a single
quote (apostrophe) or format the cell as Text.
--
Gary's Student


"BethP" wrote:

We're seeing a problem in Excel that I've never come across. All of the
machines that we're seeing this on just downloaded this month's auto-update,
but I haven't looked yet to see if there were any Office fixes. I'm
wondering if any of you are seeing this or might have a solution for me.

We typically work with 16-digit credit card-style numbers, where the entire
number is treated as text. Typically, trying to format the number as text
results in scientific notation, so I usually leave it as a number with no
commas or decimals. However, no amount of formatting seems to affect this
issue.

When I enter a number in to a cell, if it is longer then 15 digits, any
number after 15 changes to a zero. You can see in the example I've pasted
below where I was typing in all 4's ending with a 3, from 2 digits to 17
digits long. Even if you go to edit the data, the last numbers have been
changed to a 0, it's not just how the formatting is showing the number.

43
443
4443
44443
444443
4444443
44444443
444444443
4444444443
44444444443
444444444443
4444444444443
44444444444443
444444444444443
4444444444444440
44444444444444400

Am I missing something? Help!

Thanks!!
beth

To add to what Gary wrote - you can usually simply change the name of a .csv
file to .txt and work with it as a text file that way. So if your source
says "I can't give you anything but a .csv file", try just changing the name
after you receive it.

"Gary''s Student" wrote:

Hi Beth:

Arvi's approach is very good. Another trick is to control things when the
data gets pulled in.

If possible, get the data as a text file with a .txt type rather than .csv
When you open a .txt file in Excel, the Import Wizard will be invoked. You
can tell the Wizard to treat that field as text.
--
Gary's Student


"BethP" wrote:

Thanks for the quick reply. It's so weird that I've never run in to this
before!

If you pull in a list of data with 16-digit numbers, and perform a cell
format to set it all to text, by default it converts to scientific formula.
Is there a way to keep this from happening without having to concatenate
every line with an apostrophe? (We occasionally pull 1000 row x 30 column
spreadsheets with this kind of data, and it's often by techs who are not all
that Excel savvy.)

Thanks again,
beth

"Gary''s Student" wrote:

Hi Beth:

This is not a new problem, it is an old limitation. Integer numbers can
have 15 digits. If you need more than 15, just precede the value by a single
quote (apostrophe) or format the cell as Text.
--
Gary's Student


"BethP" wrote:

We're seeing a problem in Excel that I've never come across. All of the
machines that we're seeing this on just downloaded this month's auto-update,
but I haven't looked yet to see if there were any Office fixes. I'm
wondering if any of you are seeing this or might have a solution for me.

We typically work with 16-digit credit card-style numbers, where the entire
number is treated as text. Typically, trying to format the number as text
results in scientific notation, so I usually leave it as a number with no
commas or decimals. However, no amount of formatting seems to affect this
issue.

When I enter a number in to a cell, if it is longer then 15 digits, any
number after 15 changes to a zero. You can see in the example I've pasted
below where I was typing in all 4's ending with a 3, from 2 digits to 17
digits long. Even if you go to edit the data, the last numbers have been
changed to a 0, it's not just how the formatting is showing the number.

43
443
4443
44443
444443
4444443
44444443
444444443
4444444443
44444444443
444444444443
4444444444443
44444444444443
444444444444443
4444444444444440
44444444444444400

Am I missing something? Help!

Thanks!!
beth

In fact no extension will work, basically anything but *.csv

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
To add to what Gary wrote - you can usually simply change the name of a
.csv
file to .txt and work with it as a text file that way. So if your source
says "I can't give you anything but a .csv file", try just changing the
name
after you receive it.

"Gary''s Student" wrote:

Hi Beth:

Arvi's approach is very good. Another trick is to control things when
the
data gets pulled in.

If possible, get the data as a text file with a .txt type rather than
.csv
When you open a .txt file in Excel, the Import Wizard will be invoked.
You
can tell the Wizard to treat that field as text.
--
Gary's Student


"BethP" wrote:

Thanks for the quick reply. It's so weird that I've never run in to
this
before!

If you pull in a list of data with 16-digit numbers, and perform a cell
format to set it all to text, by default it converts to scientific
formula.
Is there a way to keep this from happening without having to
concatenate
every line with an apostrophe? (We occasionally pull 1000 row x 30
column
spreadsheets with this kind of data, and it's often by techs who are
not all
that Excel savvy.)

Thanks again,
beth

"Gary''s Student" wrote:

Hi Beth:

This is not a new problem, it is an old limitation. Integer numbers
can
have 15 digits. If you need more than 15, just precede the value by
a single
quote (apostrophe) or format the cell as Text.
--
Gary's Student


"BethP" wrote:

We're seeing a problem in Excel that I've never come across. All
of the
machines that we're seeing this on just downloaded this month's
auto-update,
but I haven't looked yet to see if there were any Office fixes.
I'm
wondering if any of you are seeing this or might have a solution
for me.

We typically work with 16-digit credit card-style numbers, where
the entire
number is treated as text. Typically, trying to format the number
as text
results in scientific notation, so I usually leave it as a number
with no
commas or decimals. However, no amount of formatting seems to
affect this
issue.

When I enter a number in to a cell, if it is longer then 15 digits,
any
number after 15 changes to a zero. You can see in the example I've
pasted
below where I was typing in all 4's ending with a 3, from 2 digits
to 17
digits long. Even if you go to edit the data, the last numbers have
been
changed to a 0, it's not just how the formatting is showing the
number.

43
443
4443
44443
444443
4444443
44444443
444444443
4444444443
44444444443
444444444443
4444444444443
44444444444443
444444444444443
4444444444444440
44444444444444400

Am I missing something? Help!

Thanks!!
beth