return left most part of cell

anyone know if there is a formula that can return the first word of a string

example: 1

Abelia grandiflora 'Sherwoodii'

return - Abelia

example: 2

Abies balsamea

return - Abies

example: 3

Abutilon

return - Abutilon


what is needed is one formula that will cover all string possibilities

Thnkyou if you can help.

Try this:

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

"Pat" wrote:

> anyone know if there is a formula that can return the first word of a string
>
> example: 1
>
> Abelia grandiflora 'Sherwoodii'
>
> return - Abelia
>
> example: 2
>
> Abies balsamea
>
> return - Abies
>
> example: 3
>
> Abutilon
>
> return - Abutilon
>
>
> what is needed is one formula that will cover all string possibilities
>
> Thnkyou if you can help.
>
>
>

Thanks David, it works for example 1 & 2

#VALUE! is returned for 3


"David Hepner" <[email protected]> wrote in message
news:[email protected]...
> Try this:
>
> =LEFT(A1,FIND(" ",A1,1)-1)
>
> "Pat" wrote:
>
> > anyone know if there is a formula that can return the first word of a
string
> >
> > example: 1
> >
> > Abelia grandiflora 'Sherwoodii'
> >
> > return - Abelia
> >
> > example: 2
> >
> > Abies balsamea
> >
> > return - Abies
> >
> > example: 3
> >
> > Abutilon
> >
> > return - Abutilon
> >
> >
> > what is needed is one formula that will cover all string possibilities
> >
> > Thnkyou if you can help.
> >
> >
> >

On Fri, 9 Sep 2005 13:54:29 +0100, "Pat" <[email protected]>
wrote:

>Thanks David, it works for example 1 & 2
>
>#VALUE! is returned for 3


Try modifying David's suggestion to:

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


Rgds



__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Sorry, I didn't test all three. This should work.

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

"Pat" wrote:

> Thanks David, it works for example 1 & 2
>
> #VALUE! is returned for 3
>
>
> "David Hepner" <[email protected]> wrote in message
> news:[email protected]...
> > Try this:
> >
> > =LEFT(A1,FIND(" ",A1,1)-1)
> >
> > "Pat" wrote:
> >
> > > anyone know if there is a formula that can return the first word of a
> string
> > >
> > > example: 1
> > >
> > > Abelia grandiflora 'Sherwoodii'
> > >
> > > return - Abelia
> > >
> > > example: 2
> > >
> > > Abies balsamea
> > >
> > > return - Abies
> > >
> > > example: 3
> > >
> > > Abutilon
> > >
> > > return - Abutilon
> > >
> > >
> > > what is needed is one formula that will cover all string possibilities
> > >
> > > Thnkyou if you can help.
> > >
> > >
> > >
>
>
>

On Fri, 9 Sep 2005 13:36:37 +0100, "Pat" <[email protected]> wrote:

>anyone know if there is a formula that can return the first word of a string
>
>example: 1
>
>Abelia grandiflora 'Sherwoodii'
>
>return - Abelia
>
>example: 2
>
>Abies balsamea
>
>return - Abies
>
>example: 3
>
>Abutilon
>
>return - Abutilon
>
>
>what is needed is one formula that will cover all string possibilities
>
>Thnkyou if you can help.
>


=LEFT(A1,FIND(" ",A1&" ")-1)

(will also work on the last example)


--ron