Tom’s Tutorials For Excel: Extracting Text to Left of the Second Space (or Specified Character) in a String

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))),",","")

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
14 comments on “Tom’s Tutorials For Excel: Extracting Text to Left of the Second Space (or Specified Character) in a String
  1. Bruce says:

    I AM TRYING YOUR FORMULA OUT IN EXCEL 2010 AND IT ONLY GIVES ME UP TO THE FIRST SPACE….

    BUT WHEN I USE THE FOLLOWING: =LEFT(D2,FIND(“*”,SUBSTITUTE(D2,” “,”*”,LEN(D2)-LEN(SUBSTITUTE(D2,” “,””))))-1) I GET EVERYTHING IN THE CELL….

    WHAT DO I NEED TO DO TO GET EVERYTHING UP TO THE SECOND SPACE IN THE CELL?

    • Tom Urtis says:

      My formula works just fine. It will return the text up to and including the second spacebar character in a cell; that is, extracting everything to the left of the second spacebar character of said text, as the title of the blog post topic states.

      Just now I opened a new workbook, and in cell A2 I entered
      My name is Tom Urtis.

      In cell D2 I entered the formula straight from my blog post
      =SUBSTITUTE(TRIM(LEFT(A2,FIND(” “,A2&” “,FIND(” “,A2)+2))),”,”,””)

      In cell D2, as I expected, is this, returned by that formula:
      My name

      Something is going on with the text in your cell or the formula you actually used. Maybe what looks like an empty space in your text is really an ascii character that looks like an empty space. In any case, I cannot re-create your observation.

  2. Aryanpandith says:

    Very useful. Just awesome.

  3. joh says:

    I have text like
    123 as 12ff st sanfransisco,no 12345
    I want to extract
    123 as 12ff st
    sanfransisco
    no 12345
    by separate column

    • Tom Urtis says:

      Will the data in that column ALWAYS be in that syntax?
      It looks like in every case, you want to first return everything to the left of the 4th spacebar character, and so on.

  4. Schon Garcia says:

    Just wanted to thank you for your excel formula that substitutes/replaces text before the second space, etc. I was using a find replace formula before but for someone reason the output values wouldn’t work within some other formulas I had. This works awesome.

  5. Wayne Edmondson says:

    Hi Tom.. thanks for this tip. Before looking at your solution, I solved it this way:
    =SUBSTITUTE(MID(A2,1,SEARCH(” “,A2,SEARCH(” “,A2)+1)-1),”,”,””)
    Thanks for the lesson. Thumbs up!

    • Tom Urtis says:

      Thanks, Wayne. Both our formulas work when every string being parsed has at least 3 words in it. Your formula fails when there are only 1 or 2 words, and my formula fails when there is only 1 word. So a quick fix for those situations would be a nested IF to extract just 1 word if not space character exists, or 2 words if just 1 space character exists. Otherwise, both formulas do the job. Appreciate your input!

  6. Lori says:

    Hi, thank you for the post. I had a quick question as to how to extract substring before the second occurrence of ‘s’ and after the first occurrence of ‘:’.
    For example, if given:
    size:7 set:100
    I want to extract the value for size, which in this case 7.
    Which generic Excel formula would I be able to use to do this?
    Thank you in advance.

    • Tom Urtis says:

      Hello Lori – –

      If your example is in cell A1, this would do what you ask:
      =TRIM(MID(A1,SEARCH(":",A1)+1,SEARCH("s",A1,SEARCH("s",A1)+1)-SEARCH(":",A1)-1))

  7. Ken Skinner says:

    Mr. Urtis, can you tell me how I can tweak the formula to include the first letter to the right of the second space bar? I am trying to format names from one source to match the format of another source so I can find the names of people who match both lists. I need to include the middle initial last, if there is one, so that I can find exact matches.

  8. Rick Rothstein says:

    Assuming the text in cell A2 is never longer than 300 characters (that is what the two 300s limit), this formula will return the first two words from the cell. If there is only one word in the cell, the formula will return it. If the cell is empty, then the formula returns the empty text string (“”). If you want to return 1 or more words, simply change the 2 being multiplied by the last 300 to whatever number of words you want to retrieve from the beginning of the text…

    =TRIM(MID(SUBSTITUTE(A2,” “,REPT(” “,300)),1,2*300))

Leave a Reply

Your email address will not be published. Required fields are marked *

*