Solved: left upto space

[wibbers2000]

Hello everyone,

Is it possible to do a =left upto the first space?

i.e. Cell A1 has the words Liverpool football Club
A2 has the words Leeds Football Club

What would like to do if possible, is to use a formula or code that will allow me to bring back the left most word, ie, Liverpool or Leeds.


Regards
Wibbers

[Bob Phillips]

Is it possible to do a =left upto the first space?

i.e. Cell A1 has the words Liverpool football Club
A2 has the words Leeds Football Club

What would like to do if possible, is to use a formula or code that will allow me to bring back the left most word, ie, Liverpool or Leeds.

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

[mvidas]

Hi Wibbers,

You can do that by function or by code, your choice. For function:

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

For code:[vba]Sub Wibbers()
Dim CLL As Range
For Each CLL In Range("A1", Range("A65536").End(xlUp))
If Len(CLL.Text) > 0 Then
CLL.Offset(0, 1).Value = Left(CLL.Text, IIf(InStr(1, CLL.Text, " ") > 0, _
InStr(1, CLL.Text, " ") - 1, Len(CLL.Text)))
End If
Next
Set CLL = Nothing
End Sub[/vba]Matt

[Bob Phillips]

A b it better is

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

so that Arsenal works as well

[Shazam]

Or:


=TRIM(LEFT(A1,SEARCH(" ",A1&" ")))

[wibbers2000]

thanks to all of you for your help and advice