Home 
Search 
Today's Posts 
#11




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 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. Thank you in advance! 
#12




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 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. Thank you in advance! 
#13




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 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. Thank you in advance! 
#14




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 UTC5, 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 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. Thank you in advance! 
#15




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




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 
Display Modes  


Similar Threads  
Thread  Forum  
Converting a text word or text string to a number  Excel Discussion (Misc queries)  
pull numbers from text string  Excel Discussion (Misc queries)  
variable text value within string formula required to sum column  Excel Worksheet Functions  
Formula to extract a specific word from text string  Excel Worksheet Functions  
Pull last word from a text string in Excel  Excel Worksheet Functions 