MID & VLOOKUP Function

[engmgriff]

Hi All

I have set up a cell so that an alpa numeric number can be entered.
From this I have used the MID function to indivdually obtain eac
character and then used a VLOOKUP function to obtain data from thes
characters from another table.

However this works for all text characters but does not work for th
numeric characters. The MID function does work when getting th
numeric character and diplays it. But when I do the VLOOKUP with th
numeric character it brings back a N/A error.

If i change the lookup table with text characters instaed of numeri
characters i'm able to get the lookup function to work therefore I kno
there is nothing wrong with the way i've put the fucntion in.

I think it is something to do with the MID function obtaing the numeri
characters from the array.

Thanks for any help

Regards

Mat

 

[Paul]

Hi All

I have set up a cell so that an alpa numeric number can be entered.
From this I have used the MID function to indivdually obtain each
character and then used a VLOOKUP function to obtain data from these
characters from another table.

However this works for all text characters but does not work for the
numeric characters. The MID function does work when getting the
numeric character and diplays it. But when I do the VLOOKUP with the
numeric character it brings back a N/A error.

If i change the lookup table with text characters instaed of numeric
characters i'm able to get the lookup function to work therefore I know
there is nothing wrong with the way i've put the fucntion in.

I think it is something to do with the MID function obtaing the numeric
characters from the array.

Thanks for any help

Regards

Matt

You are quite right. MID will return characters as a text string, even if
all the characters are numeric. (You can see this if you have your MID
formula in a General-formatted cell without any particular alignment
formatting, as the characters will be left-aligned.) To convert them to a
number, use VALUE. For example,
=VALUE(MID(A1,4,3))

 

[Arvi Laanemets]

Hi

Or

You are quite right. MID will return characters as a text string, even if
all the characters are numeric. (You can see this if you have your MID
formula in a General-formatted cell without any particular alignment
formatting, as the characters will be left-aligned.) To convert them to a
number, use VALUE. For example,
=VALUE(MID(A1,4,3))

Or
=MID(A1,4,3)*1