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))),",","")
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?
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.
Very useful. Just awesome.
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
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.
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.
Thank you, Schon! I also put new Excel tips on my Twitter page every day, @tomurtis
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!
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!
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.
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))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.
With your string in cell A2:
=TRIM(MID(A2,SEARCH(” “,A2,SEARCH(” “,A2)+1),2))
So if in cell A2 is
My name is Tom Urtis
that formula returns i.
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))