zero returns

[Rickity2005]

I have an array formula which works and returns the correct value, as in the correct number.
This result is a precedent for further calculations or formulae.
My ongoing formulae result in a zero because the original answer is returned as text.
I have checked cell formats to be general or number but nothing changes.
I have confirmed that the original answer is text with =IF(ISNUMBER and the result is false.
When evaluating the next ongoing formula, I can see in the dialogue box that my original result is in quotation marks again confirming it to be text.

This file has been copied from last years workbook which was working fine and not had this issue.

How can I get my original result to return as a number?

 

[mikerickson]

To convert from a numeral (text) to a number you can add zero.

="123"+0

 

[Rickity2005]

R U SH**ING ME?
Thankyou for that. It has worked. The sad thing is I have add this zero to a lot of formulae.
I still do not understand why I have to do it like that because ,,,,,I don't know.

 

[mikerickson]

What is the original formula that is returning the text?
You can either add it to the text-result formula e.g. =MID(A1,2,3)+0

Or add it when you do arithmetic, just as ="123"+0 returns a number so also does ="123"/6. An awful lot of Excel's number functions will automatically convert e.g SIN("123"), LOG("123")

Also, the CSE formula =AND(MID(A1,ROW($1:$100),1)<"A") (entered with Ctrl-Shift-Enter) acts as a ISNUMERAL function. It will return true if A1 contains a numeral (i.e. a number in text format) or a number.

 

[Peter_SSs]

The sad thing is I have add this zero to a lot of formulae.

Depends a bit what your formula actually are, but the added zero should be just as good at the beginning so if your formulas only contain a single "=" sign at the beginning, you may be able to select all the formula cells & use Excel's Find/Replace to replace all "=" signs with "=0+" to do them all at once.

 

[Rickity2005]

What is the original formula that is returning the text?
You can either add it to the text-result formula e.g. =MID(A1,2,3)+0

Or add it when you do arithmetic, just as ="123"+0 returns a number so also does ="123"/6. An awful lot of Excel's number functions will automatically convert e.g SIN("123"), LOG("123")

Also, the CSE formula =AND(MID(A1,ROW($1:$100),1)<"A") (entered with Ctrl-Shift-Enter) acts as a ISNUMERAL function. It will return true if A1 contains a numeral (i.e. a number in text format) or a number.

The original formula is just retreiving a cell value from another worksheet formula.

I understand your explanation of ="123"+0
That has helped me with my issue. Thank you for that.

I don't understand (and don't need to at this moment) the CSE formula bit.

Thank you so much for your help.

 

[Rickity2005]

Thank you for assisting me with the +0 at the start of my formula and using "copy & replace".
I have used "copy & replace" alot in the past.
The fact you have enlightened me to put +0 at the start of my formula has shortened my work load immensely. (did I spell that right?)
Anyway, thank you so much.

I can say my problem solved.

 

[Peter_SSs]

.. immensely. (did I spell that right?)

Yes you did.
.. but this should be "find & replace"

... using "copy & replace".

Anyway, glad you seem to have saved some time!