return left most part of cell

[Pat]

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.

 

[Guest]

Try this:

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

 

[Pat]

Thanks David, it works for example 1 & 2

#VALUE! is returned for 3

 

[Guest]

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

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

 

[Richard Buttrey]

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
__________________________

 

[Ron Rosenfeld]

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