Vlookups and formats

  • Thread starter Thread starter Gandor
  • Start date Start date
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.
 
=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.
Click to expand...
 
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.
Click to expand...
 
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.
Click to expand...
Click to expand...
 
Back
Top