Is there a simple solution?

B

Bobby

Hello
I am having trouble with a lookup formula. The lookup value is in a cell
with several other comma delimited values, ie., 123456, 12345678, 123452.
The value I want to use is the portion to the left of the first comma. The
numbers will either be 6 or 8 digits. With 2 8 digit numbers I have tried
vlookup(left(mycell,lookuprange,index)) but this is returning N/A. Is there
a way to do this without doing a text to columns?
Thanks!
 
E

Elkar

Your problem may be that the results from your LEFT() function are text and
you're looking in a series of numbers. Try this:

=VLookUp(Value(Left(mycell,6),lookuprange,index))

HTH,
Elkar
 
B

Bobby

Sandy
Thanks for the reply
This looks like what I am looking for.
My cell A1 = 70592525, 70592527
Formatted as number
However your formula returns #value
What am I doing wrong?
Thanks again!
 
S

Sandy Mann

The $2 answer is I don't know. If I format the cell as Number and enter
70592525,70592527 it displays as: 7,059,252,570,592,527 and gives the
#Value! error as you say. However, if I include the space after the comma
it turns the entry into text and the formula works even although the cell is
still formatted as Number.

Try entering in anoth cell =ISTEXT(A1) and see if you get TRUE (if the entry
is really Text) or FALSE.

If you still can't get it to work then you can send me a sample sheet
privately

--
HTH

Sandy
[email protected]
[email protected] with @tiscali.co.uk
 
B

Bobby

Thanks
Im not sure what I changed but it is working now. One followup question
(information I forgot to provide in OP) The cell could be only one number.
What is the best way to handle this? I was thinknig maybe using LEN>8.
Thanks again
 
Top