Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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.

Thank you in advance!

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,722
Default 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.

Thank you in advance!

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default 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
...
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!



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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.

Thank you in advance!

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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
...
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!







  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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.

Thank you in advance!



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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
...
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!







  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default 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.

Thank you in advance!

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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
...
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!






  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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.

Thank you in advance!



  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,934
Default Formula to pull first word from text string in a column

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.

--
Rick (MVP - Excel)


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







  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Formula to pull first word from text string in a column

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

--
Biff
Microsoft Excel MVP


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









  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Formula to pull first word from text string in a column

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.


--
Biff
Microsoft Excel MVP


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








  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Formula to pull first word from text string in a column

This is great! I've been looking for this... Now, is there a way to extract the last word in a string ONLY if it is not the only word in the string. If it is then i want it to be found in the formula you wrote above. I'm basically trying to extract three names but not always is there a second or even a third name, for example, Cher. I have it all working but the last name will duplicate the first if it is a one word name. :(


On Thursday, January 8, 2009 at 2:00:56 PM UTC-5, David Biddulph wrote:
=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
...
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!


  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 18
Default Formula to pull first word from text string in a column

On Friday, 17 January 2020 03:50:19 UTC, wrote:
This is great! I've been looking for this... Now, is there a way to extract the last word in a string ONLY if it is not the only word in the string. If it is then i want it to be found in the formula you wrote above. I'm basically trying to extract three names but not always is there a second or even a third name, for example, Cher. I have it all working but the last name will duplicate the first if it is a one word name. :(


Hi
I would use a helper column.
With your data in column A, starting in A2, enter in B2
=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))
This will tell you how many spaces you have in column A
Then, in C2 enter
=LEFT(A2,FIND(" ",A2&" ")-1) and copy down to give you all first names
In D2 enter
=IF(B2=2,MID(A2,FIND(CHAR(160),SUBSTITUTE(A2," ",CHAR(160),1))+1,FIND(CHAR(160),SUBSTITUTE(A2 ," ",CHAR(160),1))-1),"")
and copy down which will extract the middle name, if there is one.
Finally in E2 enter
=IF(B20,MID(A2,FIND(CHAR(160),SUBSTITUTE(A2," ",CHAR(160),B2))+1,256),"")
and copy down, which will find the last name, if there is one.

Hope this helps.


  #16   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Formula to pull first word from text string in a column

Hello,

The =split( cell, "character") function would do this. You'll just have to make sure that you have enough columns to the right to accommodate this split.

For your example, you would need 3 blank columns to the right available.

Assuming cells are listed from A1:A4, to get the type of car, you'll need to split using a space character (" ") using the formula below.

=split(A1, " ")
This will result in
A1 = Suburu WRX in B1 = split(A1,"") and showing in B1 = Suburu C1=WRX

....

=split(A4, " ")
This will result in
A4 = Jeep Grand Cherokee in B1 = split(A4,"") and showing in B4 = Jeep C4=Grand D4 = Cherokee

I hope this helps!
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting a text word or text string to a number Tom Excel Discussion (Misc queries) 6 January 2nd 09 09:23 PM
pull numbers from text string [email protected] Excel Discussion (Misc queries) 7 March 19th 07 07:04 PM
variable text value within string formula required to sum column Tester Excel Worksheet Functions 2 December 8th 06 02:07 PM
Formula to extract a specific word from text string Dinesh Excel Worksheet Functions 4 November 3rd 06 09:35 PM
Pull last word from a text string in Excel nmp Excel Worksheet Functions 8 September 25th 06 10:13 PM


All times are GMT +1. The time now is 04:57 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"