Prev: Text data -- linking Excel sheets
Next: Can I title columns with out them having a row number?
From: Jim Thomlinson on 4 Feb 2010 17:06
Try this first...
Confirm your calculation setting.
Create a new workbook.
Add a simple formula to the new book such as the one you have. If it calc's
correctly then it is not XL but a problem in the workbook.

If the cells were previously text and you just reformatted them to number
then the underlying value will still be Text. You need to force XL to convert
them. Gord posted a method to do that.
--
HTH...

Jim Thomlinson


"Martin" wrote:

> sorry, I forgot to told you that I have tried this too, i'm stuck with my
> problem maybe unistall and reinstall Ofiice will set the problem ?
> --
> Martin Verville
>
>
> "Gord Dibben" wrote:
>
> > Sounds like your numbers are Text.
> >
> > Format to General or Number then copy an empty cell.
> >
> > Select the range of "numbers" and Edit>Paste Special>Add>OK>Esc.
> >
> >
> > Gord Dibben MS Excel MVP
> >
> > On Thu, 4 Feb 2010 11:22:01 -0800, Martin <Martin(a)discussions.microsoft.com>
> > wrote:
> >
> > >Does'nt matter A1 can be as simple as 1 and A2 as 2, and it return zero
> > >onstead of tree.
> > >
> > >I've tried with a couple different excel file in the company and i'm at the
> > >same point, always the same problem.
> >
> > .
> >
From: "David Biddulph" groups [at] on 5 Feb 2010 06:00
And of course if there is any doubt as to whether the contents are text,
ISNUMBER() and ISTEXT() will give a clue.
--
David Biddulph

"Jim Thomlinson" <James_Thomlinson(a)owfg-Re-Move-This-.com> wrote in message
news:94610521-A33C-41EF-9638-BC05D5A80F5F(a)microsoft.com...
> Try this first...
> Confirm your calculation setting.
> Create a new workbook.
> Add a simple formula to the new book such as the one you have. If it
> calc's
> correctly then it is not XL but a problem in the workbook.
>
> If the cells were previously text and you just reformatted them to number
> then the underlying value will still be Text. You need to force XL to
> convert
> them. Gord posted a method to do that.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Martin" wrote:
>
>> sorry, I forgot to told you that I have tried this too, i'm stuck with my
>> problem maybe unistall and reinstall Ofiice will set the problem ?
>> --
>> Martin Verville
>>
>>
>> "Gord Dibben" wrote:
>>
>> > Sounds like your numbers are Text.
>> >
>> > Format to General or Number then copy an empty cell.
>> >
>> > Select the range of "numbers" and Edit>Paste Special>Add>OK>Esc.
>> >
>> >
>> > Gord Dibben MS Excel MVP
>> >
>> > On Thu, 4 Feb 2010 11:22:01 -0800, Martin
>> > <Martin(a)discussions.microsoft.com>
>> > wrote:
>> >
>> > >Does'nt matter A1 can be as simple as 1 and A2 as 2, and it return
>> > >zero
>> > >onstead of tree.
>> > >
>> > >I've tried with a couple different excel file in the company and i'm
>> > >at the
>> > >same point, always the same problem.
>> >
>> > .
>> >


From: Ron Rosenfeld on 5 Feb 2010 07:03
On Thu, 04 Feb 2010 12:12:51 -0800, Gord Dibben <gorddibbATshawDOTca> wrote:

>Sounds like your numbers are Text.
>
>Format to General or Number then copy an empty cell.
>
>Select the range of "numbers" and Edit>Paste Special>Add>OK>Esc.
>
>
>Gord Dibben MS Excel MVP
>

Do the older versions work differently, Gord?

In 2007, if I, with a new workbook
Format Column A as TEXT
A1: 1
A2: 2
B1: =A1+A2
--> 3

The same occurs if I enter '1 and '2 in cells pre-formatted as General.

--ron
From: Gord Dibben on 5 Feb 2010 12:30
Ron

Tested in my 2007 with new workbook.

Format A1:A2 as Text

A1 1
A2 2

A3 =A1+A2 results in 0

Same for '1 and '2


Gord



On Fri, 05 Feb 2010 07:03:01 -0500, Ron Rosenfeld <ronrosenfeld(a)nospam.org>
wrote:

>On Thu, 04 Feb 2010 12:12:51 -0800, Gord Dibben <gorddibbATshawDOTca> wrote:
>
>>Sounds like your numbers are Text.
>>
>>Format to General or Number then copy an empty cell.
>>
>>Select the range of "numbers" and Edit>Paste Special>Add>OK>Esc.
>>
>>
>>Gord Dibben MS Excel MVP
>>
>
>Do the older versions work differently, Gord?
>
>In 2007, if I, with a new workbook
> Format Column A as TEXT
> A1: 1
> A2: 2
> B1: =A1+A2
> --> 3
>
>The same occurs if I enter '1 and '2 in cells pre-formatted as General.
>
>--ron

From: Ron Rosenfeld on 5 Feb 2010 20:50
On Fri, 05 Feb 2010 09:30:00 -0800, Gord Dibben <gorddibbATshawDOTca> wrote:

>Ron
>
>Tested in my 2007 with new workbook.
>
>Format A1:A2 as Text
>
>A1 1
>A2 2
>
>A3 =A1+A2 results in 0
>
>Same for '1 and '2
>
>
>Gord

Interesting.

I just did it again, but this time on a different computer.

Same results as I posted initially.

Computer 1 is running W7x64; computer 2 is running XP SP3 (32 bit)

XL version on computer 2 is Excel 2007 (12.0.6514.5000) SP2 MSO
(12.0.6425.1000)

I assume the version on my W7 machine is the same, but it's now a few hundred
miles away, so I can't be sure.

The other interesting phenomenon, that does occur on both machines, is that
AFTER I enter =a1+a2 and see "3" as a result (without the quotes)
the "3" is left justified
the cell is formatted as TEXT
=ISTEXT(B1) --> FALSE
=ISNUMBER(B1) --> TRUE
Selecting B1, then EDIT (F2) and <enter> displays just the formula, as
one would expect in a text formatted cell.

Very weird. But it's occurring on two different machines, two different OS's,
same Excel 2007.

--ron
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Text data -- linking Excel sheets
Next: Can I title columns with out them having a row number?