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!

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!

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
[email protected]
[email protected] with @tiscali.co.uk

"Bobby" <[email protected]> wrote in message
news:[email protected]...
> 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!

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
> [email protected]
> [email protected] with @tiscali.co.uk
>
> "Bobby" <[email protected]> wrote in message
> news:[email protected]...
> > 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!
>
>
>

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


"Bobby" <[email protected]> wrote in message
news:[email protected]...
> 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
>> [email protected]
>> [email protected] with @tiscali.co.uk
>>
>> "Bobby" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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!
>>
>>
>>

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

"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
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> "Bobby" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >> [email protected]
> >> [email protected] with @tiscali.co.uk
> >>
> >> "Bobby" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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!
> >>
> >>
> >>
>
>
>
>
>
>

If I understand correctly try:

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

--
HTH

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


"Bobby" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "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
>> [email protected]
>> [email protected] with @tiscali.co.uk
>>
>>
>> "Bobby" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> >> [email protected]
>> >> [email protected] with @tiscali.co.uk
>> >>
>> >> "Bobby" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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!
>> >>
>> >>
>> >>
>>
>>
>>
>>
>>
>>