return left most part of cell

P

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.
 
D

David Hepner

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

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

Richard Buttrey

Thanks David, it works for example 1 & 2

#VALUE! is returned for 3
Click to expand...


Try modifying David's suggestion to:

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


Rgds



__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
R

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.
Click to expand...


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

(will also work on the last example)


--ron
 
Top