excel question Formats and number pad

R

raindancer

A colleague of mine mentioned that when entering data with the number
pad as opposed to the row of numbers at the top of the keyboard the
format of the cells is changed to text. The problem I am having is
that our Chip # column (each tagged animal has a unique chip # for
Identification purposes) may consist of numbers alone of numbers and
letters. When I make a pivot table it does not class the number
6443322 with all the 6443322's if one is formatted in text and one a
number. I get the error green corner of the cell saying number
formatted as text and if I change it it still will not work. Does
anyone have experience with this that I can solce my problem?
Thank you for your help!
K
 
D

Dave Peterson

I don't think your colleague is correct about the number keypad.

But I would try to keep all my data the same -- all numbers or all text.

If you want to convert the text numbers to number numbers, you can:
select an empty cell
edit|copy
select the range to fix
edit|paste special|check add

Excel will coerce the text numbers to number numbers.

If you want to convert the number numbers to text numbers, you can use a helper
column and use something like:

=a2&""
or
=text(a2,"000000000")
(use a numberformat you like)

Then copy|paste special|values over the original range and delete the helper
column.

I'd format that column as Text (format|cells|number tab) so that any new entry
would be treated as text.
 
Top