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!
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
i do have a question i'm trying to set the function to extract text from between two characters for ex: blah-blah-blah-blah_wordtoextract-moretext. i used your function =MID(A2,FIND("*",SUBSTITUTE(A2,"_"," * ",1)),5) but the names i need to extract go beyond 5 characters. how do i set the formula to only look for text between the "_" and "-"?
The formula below works for your example Erin. =LEFT(RIGHT(A2,LEN(A2)-FIND("_",A2)),FIND("-",RIGHT(A2,LEN(A2)-FIND("_",A2)))-1) It uses the RIGHT function to extract everything after the _ and then the LEFT function from that result to extract everything before the -.
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.
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
Ankan123 Jyoti You will need to locate the end of the string using Find and Substitute again maybe like this vid. Then subtract the position of first character from position of last character.
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?
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.
We would need to also find the end of the characters to extract, and subtract the starting position to leave us with the number of characters. This would be used in the place of the 2. I have this video on extracting text between two characters which may also help - ua-cam.com/video/Yf-aqzuuQzE/v-deo.html
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?
Absolutely. You could use Power Query to import data from the web if you need a way, and this is refreshable. And then wit the formula you already have, possibly combined with something like this - ua-cam.com/video/Yf-aqzuuQzE/v-deo.html to locate the end of the numbers too. Then the length of numbers will not affect things.
@@Computergaga , thanks, i managed to find a way, had to use multiple formulas to get the results that i wanted, but it works now the way i want it to. i have been having a problem though with the data web query. as of late it keeps telling me that "the site reports that the request is not valid". any ideas on how to solve this issue as the hyperlink i am using does work in my browser but i cant get it to work in excel, even though it did at the beginning. all the formulas i put together to extract information from the text from the link is useless now as the link wont auto refresh to update the information i need. i would really appreciate any help i can get. have searched the web for days now and cant get a clear answer as to why its doing this, what is the cause or how to fix it so that the links work again. thanks in advance for any help or advice you can provide me with.
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.
@@Computergaga 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)&"~")))
The SUBSTITUTE function would be used to remove characters using a formula. If you want to remove them from the current cells, then use the Find and Replace tool.
Hi Monika, the following will work. On th question where we entered 2, we use the FIND and SUBSTITUTE again to find the difference between the fourth occurrence of a space and the third occurrence of a space. =MID(A1,FIND("*",SUBSTITUTE(A1," ","*",3)),FIND("?",SUBSTITUTE(A1," ","?",4))-FIND("*",SUBSTITUTE(A1," ","*",3)))
@@Computergaga extract number from a text : * replace A2 with whatever destination of extraction you need =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) Take care
I don't completely understand the question Imran. You have a list of names with and without spaces and a lakh in the same column. And you want the names without spaces? Sounds complex. Depending on the names involved, possibly almost impossible to automate as names are so random in length and structure.
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)
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.
Thank you Matt. SUBSTITUTE is such a useful function, there are many instances of its benefits. When compared to using Text to Columns instead, the key difference is that the formula automates the solution. Someone would need to manually perform Text to Columns each time. So it all depends on the regularity of the task.
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)&"~")))
THANK YOU! This is the ONLY video that has helped!
You're welcome! Glad I could help.
Excellent tutorial, Computergaga, the explanation can't be clearer. Thanks for sharing your knowledge!!
Super helpful! Very easy to follow and understand - thank you
Thank you Verity.
This video was awesome. Normally these tutorials bore me a bit... This one was so entertaining, haha. Love your energy!
Thank you very much, Gabriela.
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!
What about if the "3" isn't fixed.... but it could be; 3,5,12,1,4 spaces from the left?
a 7-year-old video I stumbled upon which helped me immensely! thank you!
Excellent! You're very welcome.
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
Thank you very much Harshad.
came for the lesson stayed for the accent. this is brilliant and helpful; you approached it with the right attitude.
Thanks Erin.
i do have a question i'm trying to set the function to extract text from between two characters for ex: blah-blah-blah-blah_wordtoextract-moretext. i used your function =MID(A2,FIND("*",SUBSTITUTE(A2,"_"," * ",1)),5) but the names i need to extract go beyond 5 characters. how do i set the formula to only look for text between the "_" and "-"?
The formula below works for your example Erin.
=LEFT(RIGHT(A2,LEN(A2)-FIND("_",A2)),FIND("-",RIGHT(A2,LEN(A2)-FIND("_",A2)))-1)
It uses the RIGHT function to extract everything after the _ and then the LEFT function from that result to extract everything before the -.
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.
Thank you!
My pleasure Soumya.
Love it thanks!
You're very welcome. Thank you Oliver.
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
Beautifull
😊👍
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!!
Ankan123 Jyoti You will need to locate the end of the string using Find and Substitute again maybe like this vid. Then subtract the position of first character from position of last character.
Computergaga Thanks.. Bcoz of your tutorial I figured to manage my excel.. Highly appreciated.
@@ankan123jyoti3 Can you please help. I have the same query.
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?
Super!! Thanks
Welcome 😊
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.
We would need to also find the end of the characters to extract, and subtract the starting position to leave us with the number of characters. This would be used in the place of the 2.
I have this video on extracting text between two characters which may also help - ua-cam.com/video/Yf-aqzuuQzE/v-deo.html
thank you
You're welcome Yay.
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?
Absolutely. You could use Power Query to import data from the web if you need a way, and this is refreshable. And then wit the formula you already have, possibly combined with something like this - ua-cam.com/video/Yf-aqzuuQzE/v-deo.html to locate the end of the numbers too. Then the length of numbers will not affect things.
@@Computergaga , thanks, i managed to find a way, had to use multiple formulas to get the results that i wanted, but it works now the way i want it to. i have been having a problem though with the data web query. as of late it keeps telling me that "the site reports that the request is not valid". any ideas on how to solve this issue as the hyperlink i am using does work in my browser but i cant get it to work in excel, even though it did at the beginning. all the formulas i put together to extract information from the text from the link is useless now as the link wont auto refresh to update the information i need.
i would really appreciate any help i can get. have searched the web for days now and cant get a clear answer as to why its doing this, what is the cause or how to fix it so that the links work again. thanks in advance for any help or advice you can provide me with.
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.
Sure. You would need a way to recognise the end of the words. Either a delimiter or a number of words.
@@Computergaga
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)&"~")))
Hello. Instead of extracting, what function would you use to delete those two characters?
The SUBSTITUTE function would be used to remove characters using a formula.
If you want to remove them from the current cells, then use the Find and Replace tool.
IN TIME OF PANDEMY, SEE RESOLUTION: =SUBSTITUTE(A13,MID(A13,FIND("*",SUBSTITUTE(A13;" ","*",3))+1,2),"")
Awesome vedio sir
Thank you very much Mohan.
If number start with on letter also please
What if the number can be 1 digit or 3 digits and more instead of only 2? How would that work?
Hi Monika, the following will work. On th question where we entered 2, we use the FIND and SUBSTITUTE again to find the difference between the fourth occurrence of a space and the third occurrence of a space.
=MID(A1,FIND("*",SUBSTITUTE(A1," ","*",3)),FIND("?",SUBSTITUTE(A1," ","?",4))-FIND("*",SUBSTITUTE(A1," ","*",3)))
@@Computergaga extract number from a text :
* replace A2 with whatever destination of extraction you need
=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)
Take care
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
I don't completely understand the question Imran. You have a list of names with and without spaces and a lakh in the same column. And you want the names without spaces?
Sounds complex. Depending on the names involved, possibly almost impossible to automate as names are so random in length and structure.
if i will try extract 38 Orange from the sentence red yellow blue 38 Orange , how is formula
I would use Power Query. It has an option to split column from a number.
Great sir, but it is out of excel formula
IN TIME OF PANDEMY, SEE RESOLUTION: =SUBSTITUTE(A13,MID(A13,FIND("*",SUBSTITUTE(A13;" ","*",3))+1,2),""), THE 38 WILL DELETED
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)
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.
Thank you Matt. SUBSTITUTE is such a useful function, there are many instances of its benefits.
When compared to using Text to Columns instead, the key difference is that the formula automates the solution. Someone would need to manually perform Text to Columns each time. So it all depends on the regularity of the task.
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)&"~")))
Sir,
I have need first space & second space between text
Example:
GGG hhh ffff-hhh
GGG hhhh
IN TIME OF PANDEMY, ITS THE FORMULA: =LEFT(A6,FIND(" ",A6)-1)&" "&MID(A6;FIND(" ",A6)+1,(FIND(" ",A6;FIND(" ",A6)+1))-FIND(" ",A6)-1) .