changing a cell to number format using a formula

Hello,

I apologize if this is a very basic question but I am writing a template to analyze some data and I am having trouble with the formatting of my cells. I am extracting numerical values from other sheets in the workbook but I believe that Excel is not treating them as numbers. For instance, when I evaluate a formula is giving a False response to "1"<9. I believe that I can remedy this problem if I can adjust the format of the cells to 'numbers'. Is there a way to do this using formulae, or am I forced to resort to a macro?

Thank you very much for your time.

Paul

Try using the =Value("Reference")

How are you extracting the numbers? If you use quotes around numbers like "1" then they will be treated as text, also if you use functions like MID, LEFT or RIGHT then the results of these are also text, although you can adjust formulas to make them numbers, e.g.

=LEFT(A1) will always give text but, as JRock suggests, assuming the the leftmost character of A1 is numeric you can convert to a number with

=VALUE(LEFT(A1))

or by performing some sort of mathematical operation on it (without changing the value) like this

=LEFT(A1)+0

I just wanted to say thanks to JRock and daddylonglegs for providing me with the solution to my problem.

Paul