Is there a simple solution?
 

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!

Is there a simple solution?
 

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


"Bobby" wrote:

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!

Is there a simple solution?
 

If you mean just return the data before the first comma then try:

=LEFT(G4,FIND(",",G4,1)-1)

This will be as text, if you want it to be a number then use:

=--LEFT(G4,FIND(",",G4,1)-1)

--
HTH

Sandy

with @tiscali.co.uk

"Bobby" wrote in message
...
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!

Is there a simple solution?
 

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!


"Sandy Mann" wrote:

If you mean just return the data before the first comma then try:

=LEFT(G4,FIND(",",G4,1)-1)

This will be as text, if you want it to be a number then use:

=--LEFT(G4,FIND(",",G4,1)-1)

--
HTH

Sandy

with @tiscali.co.uk

"Bobby" wrote in message
...
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!

Is there a simple solution?
 

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

with @tiscali.co.uk


"Bobby" wrote in message
...
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!


"Sandy Mann" wrote:

If you mean just return the data before the first comma then try:

=LEFT(G4,FIND(",",G4,1)-1)

This will be as text, if you want it to be a number then use:

=--LEFT(G4,FIND(",",G4,1)-1)

--
HTH

Sandy

with @tiscali.co.uk

"Bobby" wrote in message
...
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!

Is there a simple solution?
 

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 LEN8.
Thanks again

"Sandy Mann" wrote:

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

with @tiscali.co.uk


"Bobby" wrote in message
...
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!


"Sandy Mann" wrote:

If you mean just return the data before the first comma then try:

=LEFT(G4,FIND(",",G4,1)-1)

This will be as text, if you want it to be a number then use:

=--LEFT(G4,FIND(",",G4,1)-1)

--
HTH

Sandy

with @tiscali.co.uk

"Bobby" wrote in message
...
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!

Is there a simple solution?
 

If I understand correctly try:

=IF(ISERROR(LEFT(A1,FIND(",",A1,1)-1)),A1,--LEFT(A1,FIND(",",A1,1)-1))

--
HTH

Sandy

with @tiscali.co.uk


"Bobby" wrote in message
...
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 LEN8.
Thanks again

"Sandy Mann" wrote:

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

with @tiscali.co.uk


"Bobby" wrote in message
...
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!


"Sandy Mann" wrote:

If you mean just return the data before the first comma then try:

=LEFT(G4,FIND(",",G4,1)-1)

This will be as text, if you want it to be a number then use:

=--LEFT(G4,FIND(",",G4,1)-1)

--
HTH

Sandy

with @tiscali.co.uk

"Bobby" wrote in message
...
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!