Sum returns zero but multiplying by a value returns a value

[REALOldNick]

I have a series of cells.They were imported and had text at the end of them. I stripped the text by removing the last 2 characters from every cell.

Now if I try to sum a series of these cell, the result is always zero. But if I multiply a cell by a value it gives the correct result. Same goes for adding the cell to a value.

What is even weirder to me is that I can add cells by asking for a1+a2+a3+a4+a5 to get a correct answer

If I can multiply the cell, doesn’t this indicate it is a number? Yet I can’t sum them.

Any help appreciated.

Nick

[EarnestAl]

The cells A1:A5 are marked as text, if you click on one of the cells you will see an apostrophe before the number in the Formula bar.

Select the cells that had text at the end.

Press Ctrl+H to open the Find and Replace dialog.

In the Find field type ^., in the Replace field type &, tick the box that says Current selection only, tick the box that says Regular Expressions.

Click the button that says Replace All and your text will become proper numbers.

[REALOldNick]

Hi and thanks for the reply.

BUT :(…if I select a cell, there is no apostrophe at the start of the field in the function bar. To strip the last two chars I used the formula =LEFT(B2,LEN(B2)-2). But even if simply manually remove the last two chars manually, I get the same result.

If I Ctrl-H, I get the Find and Replace dialog,and did what I understand you said…but I still get 0.

I ended up using Value(B3) etc

Nick

[EarnestAl]

It sounds as if the text is still text. How about =VALUE(LEFT(B2;LEN(B2)-2)). Cheers, Al

[REALOldNick]

Sorry I don’t understand. As I said in my previous comment, I used the much simpler Value (cell ref) and it worked fine.

[EarnestAl]

Sorry, I thought you were still getting zero.

However, if you are converting a mixed number and text to just number, it is better to have the number value in the conversion rather than having to add Value() to all subsequent formulas.