Cell not recognised as a number when using Sum function, or AutoCalc

[Origin528]

Dear All,

I am using Excel 2010

I have a spreadsheet with two tabs. Tab A has calculations in it and Tab B refers to the cells in Tab A. For example, Tab A has a formula in Cell F16 to extract numbers from a number and text combination in E16, {=LEFT(E16, LEN(E16)-3)}. I then get that number into Tab B with {='Tab A'!F16}. The right numbers are displayed in Tab B. I then SUM the values in Tab B for use in other formulas. However, not all the values are included in the SUM. Even the AutoCalc at the bottom of the screen doesn't recognise the values as numbers. I noticed when I copied the values and pasted them somewhere else as numbers, the ones that aren't recognised in the SUM are left justified, meaning that they could be text although they are formatted as general. Is there any way to ensure that the numbers that look like numbers and are either formatted as Numbers or General are treated as numbers?

Any suggestions?

[AliGW]

Do you have a sample workbook?

[morevijay1982]

there must be space or non numeric character in formula result
copy and paste(choose Value option )all formula result in one column
try to align cell for right alignment you will find difference in cell which contain non numeric charachter

[Tony Valko]

=LEFT(E16, LEN(E16)-3)

The LEFT function returns a TEXT value even if it looks like a number.

One way to convert the text number to a numeric number:

=--LEFT(E16, LEN(E16)-3)

[Origin528]

Thanks Tony. Problem solved by putting -- in front of the formula. Thanks.
Haven't had enough experience to work out how to note that this is solved.

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.