Excel 2007 -- What's up with this 2?

[JR]

So here I am in an imported from a text file excel workbook/sheet and
formulas don't work! (call this "First Instance")

I can not even get the cell to do a simple =1*2!!!!!!!!

I go to a new excel workbook/sheet and formulas work!

In the new work sheet, if I multiply the cell in the first instance, the
formula works -- no matter how the cell in "First Instance" is formatted
(text or number)!!???!!!!

So why not in the first instance?



Thanks!

 

[google]

There may be leading or trailing spaces or some such other difference
that is forcing the cell contents to be images of numbers (e.g. text
format) instead of actual numbers (e.g. integers, dates, etc that can
be mathematically manipulated).

Try wrapping the errant cells in the =VALUE(<cell>) function to force
conversion to the number format for manipulation.

I've seen this behaviour randomly, but usually on imports from .CSVs
or .TXTs.

(perhaps the numbers imported are wrapped in quotes within the
original .TXT...?)

 

[JR]

Thanks,
I'll give it a shot.

 

[Shane Devenshire]

Hi,

Problem:
When numbers are enter as text they may not calculate within formulas as
they should. A few formulas will work fine despite the numbers being entered
as text. Numbers can be stored as text by 1. preformatting the cell to Text
and entering the number, 2. Typing an apostrophy in front of the number '123,
3. Because the data was downloaded from a soure inwhich it was stored as a
text number, 4. Because you used the Text to Columns command and converted it
to text., and....

There is no sure indicator that a number is stored as text, although numbers
are usually right aligned and text left aligned, this may not be the case.
If you are using a later version of Excel, Error Checking green triangles may
appear at the top left corner of these cell, but this feature may be off or
the version of Excel may not support it. (2000 and earlier).

You can find out what data type the entries are by using the =ISTEXT(A1) or
=ISNUMBER(A1) functions. You can not tell by checking the Format. If a
number was entered in a cell preformatted as General or as a number, then it
will be a number, even if it's current format is Text. Likewise a number
entered in a cell preformatted as Text will be text even if it's current
format is Number, General, Date, Currency and the like.

Solution:
1. Change the format to one that is numeric and then reenter the numbers
(too slow and error prone.)
2. Select the cells and open the Error Checking options and choose Convert
to Numbers.
3. Select an empty cell and copy it. Select the text number cell and choose
Edit, Paste Special, Add (or Subtract). This method is ~100 times faster
than #2.

Dates are numbers, and if they are stored as text, you will not get an Error
Checking triangle, so method #3 is obligatory if there is a substantial
number of dates to convert.

If this information is helpful, please click the Yes button.
Cheers,
Shane Devenshire

 

[JR]

OK, apparently I didn't do a very good job of explaining what I am
experiencing so here is another try:

I imported data from a text file.

one of the elements are seriel numbers that are not numbers.
They are long -- thirteen characters long some of which lead with zeros.
While the cells are formatted as text (determined while importing) the
seriel numbers are presented as / shown as / look like numbers in scientific
notation (1.2345 10E (sic)).
I can not get them to view as seriel numbers UNLESS I open every cell and
close it (or when in the cell enter an appostrophe and close it).
This is, with many thousands of records, not the number one solution.

ALSO -- I tried entering a column and putting an appostrophe in that row and
concatenating that row with the seriel number row in another new column BUT
the new column just shows the formula and doesn't do any concatenating.

I must be missing something, but what is it?

Thanks!

 

[google]

OK, I think I'm on to something...

If you simply wish to remove the scientific notation formatting, how
about Format->Cells->Number->Custom and set Type to 0000000000000
(thirteen zeroes).

That would remove the scientific notation formatting and force leading
zeroes into your serial numbers. They would remain in the number
format for greater manipulation (e.g. sorting, etc) than text format.