Formula to pull first word from text string in a column

[CrisT]

Hello, I was hoping someone could help me with a formula. I have a column in
a spreadsheet that has the following types of text (for example):

Subaru WRX
Subaru STI
Jeep Wrangler
Jeep Grand Cherokee

What I am looking for is a formula to pull the first word from the column
and put it in another column, say column T.

Thank you in advance!

 

[Luke M]

assuming your data is in column A.

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

 

[David Biddulph]

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

You may want to add some error handling if there isn't always more than one
word.
=IF(ISNUMBER(FIND(" ",A2)-1),LEFT(A2,FIND(" ",A2)-1),A2)

 

[CrisT]

Thank you Luke, it does work. I'm sorry I should have mentioned that I
wanted to run this formula down column "T". When I ran the formula this is
what I got for the rest of the rows:

subaru WRX subuaru
subuaru STI Jeep
Jeep Wrangler Jeep
Jeep Grand Cherokee subaru
subaru WRX subuaru
subuaru STI Jeep
Jeep Wrangler Jeep
Jeep Grand Cherokee #VALUE!

CrisT

 

[T. Valko]

You may want to add some error handling

Another way:

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

 

[Pete_UK]

I think your data must start in row 1, whereas Luke's formula assumed it
started on row 2. It gave you the correct result first time because you have
Subaru on the first two rows.

Just change Luke's references from A2 to A1 in T1, and then copy down again.

Hope this helps.

Pete

 

[Pete_UK]

Hey, that's neat Biff !!

Pete

Another way:

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

 

[Shane Devenshire]

Hi,

There is nothing wrong with the stated approach.

First make sure that the formula is refering to the cell on the same row as
the formula. In other words
=LEFT(A1,Find(" ",A1)-1)
should be entered in B1, if the first entry is in A1.

 

[CrisT]

May I ask, what does the '&" "' do? Thanks for your help too!

 

[CrisT]

Thanks everyone for your help, I had tried to figure this one out on my own
by searching some old posts but I'm still trying to understand some of
"language" of formulas and got frustrated.

Thanks again!

 

[Rick Rothstein]

It makes sure the FIND function has something to find, hence no error
checking is needed. Also, as an aside, the LEFT function will return the
correct value even if you specify a length larger than the length of the
string you are applying the LEFT function to... so, when there no space in
A1, this FIND function will return a value one greater than the length of
the text in A1 and the LEFT function will just return the entire text
string.

 

[T. Valko]

I have to makeup for my "brain cramps" that I suffered yesterday!

 

[T. Valko]

That prevents an error from being returned if the string doesn't contain a
space character. That may not apply in your situation but I thought I'd
suggest it for anyone that might be interested.

A1 = Jeep

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

That formula will return an error because FIND can't find a space character
in the string. To prevent the error we append a space character: A1&" "

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

A1 = Chevy Camaro

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

That string does contain a space character and the space character that gets
appended is basically ignored because FIND will find the *first* space
character in the string.