CODEWITHSUNDEEP
excelworksheet-function
Ian Vink
Ian Vink

Reputation: 68810

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 cell.

Ian

Upvotes: 14

Views: 138369

Answers (5)

Kamil K. Wawrzyszko
Kamil K. Wawrzyszko

Reputation: 11

I found this on exceljet.net and works for me:

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

Upvotes: 1

John C
John C

Reputation: 101

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

Upvotes: -1

Peter Walke
Peter Walke

Reputation: 2518

If you want to cater to 1-word cell, use this... based upon astander's 

=IFERROR(LEFT(A1,SEARCH(" ",A1)-1),A1)

Upvotes: 10

Mark
Mark

Reputation: 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,

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166506

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

Upvotes: 35

Related Questions