Remember Me?

#1
January 8th 09, 06:35 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Oct 2006 Posts: 9
Formula to pull first word from text string in a column

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.

#2
January 8th 09, 06:47 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Dec 2007 Posts: 2,722
Formula to pull first word from text string in a column

assuming your data is in column A.

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

Luke M
*Remember to click "yes" if this post helped you!*

"CrisT" wrote:

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.

#3
January 8th 09, 07:00 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Feb 2007 Posts: 8,651
Formula to pull first word from text string in a column

=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)
--
David Biddulph

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

#4
January 8th 09, 07:14 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Oct 2006 Posts: 9
Formula to pull first word from text string in a column

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

"Luke M" wrote:

assuming your data is in column A.

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

Luke M
*Remember to click "yes" if this post helped you!*

"CrisT" wrote:

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.

#5
January 8th 09, 07:22 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 15,768
Formula to pull first word from text string in a column

You may want to add some error handling

Another way:

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

--
Biff
Microsoft Excel MVP

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
=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)
--
David Biddulph

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

#6
January 8th 09, 07:24 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 8,856
Formula to pull first word from text string in a column

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

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

"Luke M" wrote:

assuming your data is in column A.

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

Luke M
*Remember to click "yes" if this post helped you!*

"CrisT" wrote:

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.

#7
January 8th 09, 07:27 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 8,856
Formula to pull first word from text string in a column

Hey, that's neat Biff !!

Pete

"T. Valko" wrote in message
...
You may want to add some error handling

Another way:

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

--
Biff
Microsoft Excel MVP

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
=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)
--
David Biddulph

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

#8
January 8th 09, 07:31 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2008 Posts: 3,346
Formula to pull first word from text string in a column

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

"CrisT" wrote:

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

"Luke M" wrote:

assuming your data is in column A.

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

Luke M
*Remember to click "yes" if this post helped you!*

"CrisT" wrote:

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.

#9
January 8th 09, 07:42 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Oct 2006 Posts: 9
Formula to pull first word from text string in a column

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

"T. Valko" wrote:

You may want to add some error handling

Another way:

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

--
Biff
Microsoft Excel MVP

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
=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)
--
David Biddulph

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

#10
January 8th 09, 07:48 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Oct 2006 Posts: 9
Formula to pull first word from text string in a column

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!

"Shane Devenshire" wrote:

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

"CrisT" wrote:

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

"Luke M" wrote:

assuming your data is in column A.

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

Luke M
*Remember to click "yes" if this post helped you!*

"CrisT" wrote:

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.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Tom Excel Discussion (Misc queries) 6 January 2nd 09 08:23 PM [email protected] Excel Discussion (Misc queries) 7 March 19th 07 06:04 PM Tester Excel Worksheet Functions 2 December 8th 06 01:07 PM Dinesh Excel Worksheet Functions 4 November 3rd 06 08:35 PM nmp Excel Worksheet Functions 8 September 25th 06 10:13 PM

All times are GMT +1. The time now is 11:50 AM.