Tom’s Tutorials For Excel: Extracting Text to Left of the Second Space (or Specified Character) in a String
Previously, I posted this example of extracting text to the left of the first space, or of some specified character.
When you need the first two words in a string, or text to the left of the second instance of some specified character, here is a formula to extract all text to the left of the second space. I added the
SUBSTITUTE function for cases, as shown in cell A5 of the picture, when a trailing comma after the second word is not wanted.
The formula in cell B2, copied down to cell B5, is
=SUBSTITUTE(TRIM(LEFT(A2,FIND(" ",A2&" ",FIND(" ",A2)+2))),",","")