If I understand correctly try:

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

--
HTH

Sandy
sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk


"Bobby" <Bobby@discussions.microsoft.com> wrote in message
news:B8B3E04B-45D3-40E9-A637-2293D99CFC5E@microsoft.com...
> 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
>> sandymann2@mailinator.com
>> Replace@mailinator.com with @tiscali.co.uk
>>
>>
>> "Bobby" <Bobby@discussions.microsoft.com> wrote in message
>> news:B22B4D82-C765-425D-9F4E-E956417450B4@microsoft.com...
>> > 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
>> >> sandymann2@mailinator.com
>> >> Replace@mailinator.com with @tiscali.co.uk
>> >>
>> >> "Bobby" <Bobby@discussions.microsoft.com> wrote in message
>> >> news:8F6CF97A-CA0C-4656-83E6-7EF1E785167D@microsoft.com...
>> >> > 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!
>> >>
>> >>
>> >>
>>
>>
>>
>>
>>
>>