Excel function to get first word from sentence in other cell
Excel: What function can I use to take the all the characters from the beginning to the first '<". I am trying to strip out the first word from A1 and put it into B1
Eg:
A1
Toronto<b> is nice
I want "Toronto" in the next cel开发者_StackOverflow社区l.
Ian
How about something like
=LEFT(A1,SEARCH(" ",A1)-1)
or
=LEFT(A1,SEARCH("<b>",A1)-1)
Have a look at MS Excel: Search Function and Excel 2007 LEFT Function
If you want to cater to 1-word cell, use this... based upon astander's
=IFERROR(LEFT(A1,SEARCH(" ",A1)-1),A1)
I found this on exceljet.net and works for me:
=LEFT(B4,FIND(" ",B4)-1)
A1 A2
Toronto<b> is nice =LEFT(A1,(FIND("<",A1,1)-1))
Not sure if the syntax is correct but the forumla in A2 will work for you,
Generic solution extracting the first "n" words of refcell string into a new string of "x" number of characters
=LEFT(SUBSTITUTE(***refcell***&" "," ",REPT(" ",***x***),***n***),***x***)
Assuming A1 has text string to extract, the 1st word extracted to a 15 character result
=LEFT(SUBSTITUTE(A1&" "," ",REPT(" ",15),1),15)
This would result in "Toronto" being returned to a 15 character string. 1st 2 words extracted to a 30 character result
=LEFT(SUBSTITUTE(A1&" "," ",REPT(" ",30),2),30)
would result in "Toronto is" being returned to a 30 character string
精彩评论