Vlookups and formats


G

Gandor

I am using the following formula to pull in the worksheet name into cell B5

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

The worksheet name is an account number.

I then use vlookups to look at B5 as the lookup value to retreive various
points of data linked to that specific account number.

The problem I have is the formula shown adove brings the sheet name in as
text and the vlookup sees it as text. I know this because when I edit the
function the returned value for the lookup value is in quotes "1234". If I
type in 1234 in a number format it works fine.

My questions is there a function I can embed the formula above into so it
returns a number format.
 
Ad

Advertisements

D

Dave Peterson

=val(mid(....))
will convert that text number to a real number.

So will:
=--(mid(...))
(the first minus makes it a number (but opposite in sign), the second minus
changes it back to the original sign)
I am using the following formula to pull in the worksheet name into cell B5

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

The worksheet name is an account number.

I then use vlookups to look at B5 as the lookup value to retreive various
points of data linked to that specific account number.

The problem I have is the formula shown adove brings the sheet name in as
text and the vlookup sees it as text. I know this because when I edit the
function the returned value for the lookup value is in quotes "1234". If I
type in 1234 in a number format it works fine.

My questions is there a function I can embed the formula above into so it
returns a number format.
 
D

Dave Peterson

You could also use:
=1*(mid(...))
or
=0+(mid(...))

Or anything that treats that text number as a number (without changing the
value).
I am using the following formula to pull in the worksheet name into cell B5

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

The worksheet name is an account number.

I then use vlookups to look at B5 as the lookup value to retreive various
points of data linked to that specific account number.

The problem I have is the formula shown adove brings the sheet name in as
text and the vlookup sees it as text. I know this because when I edit the
function the returned value for the lookup value is in quotes "1234". If I
type in 1234 in a number format it works fine.

My questions is there a function I can embed the formula above into so it
returns a number format.
 
Ad

Advertisements

G

Gandor

Thanks Dave that was what I was looking for. I was thinking to complex and
just needed another look. Thanks Again.

Dave Peterson said:
You could also use:
=1*(mid(...))
or
=0+(mid(...))

Or anything that treats that text number as a number (without changing the
value).
I am using the following formula to pull in the worksheet name into cell B5

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

The worksheet name is an account number.

I then use vlookups to look at B5 as the lookup value to retreive various
points of data linked to that specific account number.

The problem I have is the formula shown adove brings the sheet name in as
text and the vlookup sees it as text. I know this because when I edit the
function the returned value for the lookup value is in quotes "1234". If I
type in 1234 in a number format it works fine.

My questions is there a function I can embed the formula above into so it
returns a number format.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top