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

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

On Fri, 9 Sep 2005 13:36:37 +0100, "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.



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

(will also work on the last example)


--ron