Extract Text From Nth Occurrence of a Space in a Cell 

What about if the "3" isn't fixed.... but it could be; 3,5,12,1,4 spaces from the left?

Excellent tutorial, Computergaga, the explanation can't be clearer. Thanks for sharing your knowledge!!

Thank you!

Good job, but maybe you need a certain text like 3542 or 5 .... there you go as an assist to this problem : =SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)) * ROW(INDIRECT("1:"&LEN(A2))), 0), ROW(INDIRECT("1:"&LEN(A2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A2)))/10)

Super!! Thanks

thank you

Awesome vedio sir

One question here we are hard coding the number of characters to be extracted to 2. If my length is varying, how to make it dynamic. Like i have numbers like 38,532,6554. I want the code to extract these.

Super helpful! Very easy to follow and understand - thank you

If number start with on letter also please

This is good, but really misses the power of doing a substitute to count spaces. In this example there's 0 reason to not just copy the column, text to columns, and grab the 4th column since the formula completely relies on the data you want always being after the 4th space from the beginning. Where using substitute is really useful is in text strings where there are a varying number of spaces, but your data that you want to pull out is at the beginning or end of the string and you want to disregard the middle. An example would be a string with say a customer number, then name and address, then maybe a date and amount at the end. You want the customer number, date, and amount, but the name/address information obviously has a varying number of spaces. You know the date and amount are always between the last two spaces from the end, so this formula would be modified to count the number of spaces in the cell and then return the information between the second and first space from the end, and the last space from the end.

What a great solution suggested. Thanks a lot. I had been working hard to solve such issues. In spite of my many years of experience, mathematical skills I could never find a right solution. I have developed formulas but they are very clumsy and involving so many columns. Sir, Thanks a lot as it is solved in just one statement. Excellent Tutorial

Sir, I request you to please make a formula in which I can extract two or three words from the middle of the line of a cell in Excel. I have made different formulas for both, but I am not able to form a formula by mixing them both. I am sharing both those formulas with you. You are requested to mix these two and make a formula so that I can extract two or three or four words from a line in a cell. Please Please (To extract one word from mid of line) =Trim(MID(SUBSTITUTE(B6," ",REPT(" ",LEN(B6))),(C6-1)*LEN(B6)+1,LEN(B6))) (To extract 2 or 3 word from the starting of the line) =TRIM(LEFT(B1,FIND("~",SUBSTITUTE(B1," ","~",A1)&"~")))

Nice approach Computergaga. Before assist your video until the end, I tried my own approach where I should extract all the text before the numbers. So this was the formula I came up. {=left(A1,small(if(--(mid(A1,row(indirect("1:"&len(A1))),1)=" ")*row(indirect("1:"&len(A1)))=0," ",row(indirect("1:"&len(A1)))),3)-1)} BTW nice accent. I had no problem to understand you.

Howdo you extract first, middle, last names and suffix such as Jr. into separate columns. Not everyone has a suffix nor does everyone have a middle name?

This is great thanks for replying so quickly. Do you know how to do exactly this but with a Regextract formula? This formula doesn't translate well to Google data studio unfortunately

Thanks.. Your video really helped me a lot. But what if the length of the extracted data is variable. We cannot put 2 in that case. Please help!!

What if the number can be 1 digit or 3 digits and more instead of only 2? How would that work?

came for the lesson stayed for the accent. this is brilliant and helpful; you approached it with the right attitude.

Oh, thanks Computergaga. Thank God, I found this video. I was looking for this for several days. The trick you shared with us is really helpful. Keep up the sharing!

Love it thanks!

Sir, I have need first space & second space between text Example: GGG hhh ffff-hhh GGG hhhh

This video was awesome. Normally these tutorials bore me a bit... This one was so entertaining, haha. Love your energy!

a 7-year-old video I stumbled upon which helped me immensely! thank you!

THANK YOU! This is the ONLY video that has helped!

sir I have some problem in excel I have a hudge data of names that is one lakh plus I want to found those names which have no space

Thanks sir, very helpful video. But I want to know whether we can extract more than one word. You have taught to extract one word from the middle of a line, in this video, but I want to get more than one word from a line.

Hi Computergaga, i am in need of some help, on this topic. the video gives a perfect explanation of how to get the information that i need from the string of text. but my string of text gets generated online, and will be refreshed daily where the numbers that i need extracted from the text will also change, in the way that the numbers get larger daily. is there a way to setup the formula so that it will automatically extract the number no matter how many characters it is?

Hello. Instead of extracting, what function would you use to delete those two characters?

if i will try extract 38 Orange from the sentence red yellow blue 38 Orange , how is formula

Beautifull