I have generated 2 columns of numbers using =MID(Bx,x,xx) to break up the original data, which had date and data in the same cell. I need to SUM the columns as an array, to generate a third column. All I get is 0.0000000. If I type in the contents of 2 cells, and do a SUM formula, it works. If I try to SUM two of the generated cells, identical numbers, the zero result comes up. I have formatted the cells as numbers, and put in enough decimal places.

I am using Excel 2000 9.0.7c.

When I save, it says some features may be lost. Could that be the problem.

Very grateful for any help.

I could be more precise if I saw the actual data in the cells "which had date and data in the same cell". That way I would know exactly what =MID(Bx,x,xx) was producing. The best I can offer with the limited details provided is this:

Let's say you start with this:

B C 1 01/02/2012 45 =MID(B1,12,2)C1 will display 45, but it will be the Text value 45, not the number 45. The MID function returns Text, not numbers.

Try this to force Excel to convert the Text 45 into a Number which can then be summed:

=MID(A1,12,2)*1

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Many thanks, DerbyDad. In fact, after hours of frustration, I remembered the trick of pasting the data into Notebook and copying it back into Excel. That sorted the problem. But thanks again for your efforts, very kind.

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History