Looking up half of a cell in the same sheet though

[Beth]

Try =VALUE(MID(A1,3,2))

Only need to add VALUE portion of formula if you need it
to be a number. If text is okay, then just =MID(A1,3,2)

 

[Rob]

Actually I tested that without the VALUE function, and it
returned a number, not text: the value convertion is not
needed.

 

[Peo Sjoblom]

The text functions will return a text value, if you for any reason need a
numeric
value you have to use the value function or for instance force it using an
operand
like *, /, +, - etc.

=MID(A1,3,2)+0

as an example

You can test that it is text by using the default alignment and if
it is text it will be left aligned while numeric values are right aligned
of press F5, click special and select formulas and uncheck everything
but text and you'll see that the cells will be selected

Regards,

Peo Sjoblom