Good explanations of the functions involved, and how they operate in the formulas. Nice to see you mention the colors of the () as well. They are immensely helpful in long formulas. I have never seen anyone use the substitute function the way you do. Interesting, but I would like to see a case where it is of benefit. My formulas work just fine without the substitutions. What is puzzling is that if you use SEARCH rather than FIND in your formulas, they often return errant results or an error. The difference is FIND is case sensitive, and SEARCH is not. I don't see why SEARCH fails at times. I'm glad you instructed on how to use F9 as it is a powerful tool for interpreting formulas and their results. You can simply hit the ESC key to undo the results of F9, unless you've hit Enter. Then you need Control-Z. This "/" is a slash. This "\" is a backslash, not a slash as you described.
Great use of SUBSTITUTE, FIND, LEN, LEFT, RIGHT, etc. Comes in handy with CELL("filename") function to dynamically grab the path or extract the filename of a workbook into a worksheet. Thanks for sharing this technique. Thumbs up!
the tilde character is a replacement placeholder which you will then use to extract everything before or after. as mentioned in video it must NOT be a character that is in the data string, if necessary then use a couple of characters that together are not in the string, for example "~#$@" all together is doubtful to be found in any recognisable string.
Hi Joe, please upload sample values and ask in the forum and it will be much easier to help you with a nice formula: www.teachexcel.com/talk/microsoft-office?src=yt_comment
Really helpful tutorial. My only nag is that you have to know which instance the last delimiter is at and change the instance number. But what if all of your directory paths have a different number of "\" characters, and I want to tell Excel to "find me the last one?" I tested this out using: C:\Dir\Another Dir\Some File\Example.xlsx C:\Dir\Another Dir\Some File.xlsx C:\Dir\Another Dir.xlsx And I got: =FIND("~",SUBSTITUTE(A1,"\","~",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))) This will always find the final instance of the delimiter without having to know or count it. Thus, we have: =RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1,"\","~",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))) Dragging this formula down column B will yield: Example.xlsx Some File.xlsx Another Dir.xlsx
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.
Kamesh, you can extract as much as you want! But, the formula might end up a bit interesting. Go ahead and ask this question in our forum on TeachExcel and include a description of exactly what you are trying to do and any progress that you made so far and we can get you a formula :) Forum: www.teachexcel.com/talk/microsoft-office?src=yt_comment
@@TeachExcel 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)&"~")))
SRVKOL/236/22-23 SRVKOL/5389/22-23 SRVKOL/2/22-23 SRVKOL/58/22-23 I want to extract only numbers (i.e., 236, 5389, 2, 58) using formula. Please advise. Thanks
Hi friend! I tried the last formula but I can´t , or it doesnt work with "-" , this is the complete sentence : AP - hi how are you - window and i tried with this formula : =RIGHT(C1,LEN(C1)-FIND(" - ",SUBSTITUTE(C1," - "," ~ ",2))) and is no working pleaseee help me
How can you extract multiple occurrences? For example, I am trying to extract all occurrences of the word "Scale"? In this example, Methods: Cross-sectional descriptive survey of 447 US RNs using five reliable and validated measures of presenteeism: Stanford Presenteeism Scale (SPS), Job-Stress-Related Presenteesim Scale (JSRPS), Healthcare Productivity Scale (HPS), Nurses Work Functioning Questionnaire (NWFQ), and Health and Work Questionnaire (HWQ). The survey was evaluated using descriptive, exploratory, and confirmatory factor analysis.
What if I have multiple pieces of data between multiple delimiters? i.e., xxx.xxxxx.xxxxxxxx.xxx.x.xxxx.x and I want to get data out to cells without using the text to column feature, but rather using a formula, to extract each piece of data so xxx then in the next column xxxxx then xxxxxxxx then xxx then x then xxxx then x from left to right?
Monitum: 28 dec 2019 16:07:52: auto RSP issue~monitum: 10 jan 2020 14:18:47 : ~vibhanshu singh bharti: 10 jan 2020 17:02:32 : monitor not working properly~
Hello can you help me fot my project? I want to extract the first 2 words of the file name. And if the first word is numbers. I want to extract the next 2 words to it. Pls help. I can pay thru paypal.
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)&"~")))
Hey Great Video to extract the needed. However I feel in sharing the below add-on where in every time that we are selecting the number of "\" character in the formula used. We can replace that part by using "=RIGHT(G25,LEN(G25)-FIND("~",SUBSTITUTE(G25,"/","~",LEN(G25)-LEN(SUBSTITUTE(G25,"/","")))))".
your formula returns everything after the last occurrence, and that can be done with the shorter: =TRIM(RIGHT(SUBSTITUTE(G25,"/",REPT(" ",LEN(G25))),LEN(G25)))
By far, the most enjoyable tutorial on Excel formulaes..
I have been looking for explainations on formulas for years and now I finally understand them. You are the best.
Good explanations of the functions involved, and how they operate in the formulas. Nice to see you mention the colors of the () as well. They are immensely helpful in long formulas.
I have never seen anyone use the substitute function the way you do. Interesting, but I would like to see a case where it is of benefit. My formulas work just fine without the substitutions.
What is puzzling is that if you use SEARCH rather than FIND in your formulas, they often return errant results or an error. The difference is FIND is case sensitive, and SEARCH is not. I don't see why SEARCH fails at times.
I'm glad you instructed on how to use F9 as it is a powerful tool for interpreting formulas and their results. You can simply hit the ESC key to undo the results of F9, unless you've hit Enter. Then you need Control-Z.
This "/" is a slash.
This "\" is a backslash, not a slash as you described.
You are a life savior my friend! Thank you so much for the video and for providing the example file as well!
I have inspired by your explanation. You are a great excel coach!
Thanks for the guidance. love from kerala ,india.
you were a god send man thank you !!! did my interview tasks well
Great use of SUBSTITUTE, FIND, LEN, LEFT, RIGHT, etc. Comes in handy with CELL("filename") function to dynamically grab the path or extract the filename of a workbook into a worksheet. Thanks for sharing this technique. Thumbs up!
Could not understand very well the ~ is doing, but this works for what I needed to do :) thanks!
the tilde character is a replacement placeholder which you will then use to extract everything before or after. as mentioned in video it must NOT be a character that is in the data string, if necessary then use a couple of characters that together are not in the string, for example "~#$@" all together is doubtful to be found in any recognisable string.
Best solution. Thank you.
You are a life saviour DEAR!
Glad to help! :)
really awesome video! Would love to see how you would recommend extracting the string between the 2nd and 3rd delimiter.
Hi Joe, please upload sample values and ask in the forum and it will be much easier to help you with a nice formula: www.teachexcel.com/talk/microsoft-office?src=yt_comment
Life saver you are
Superb. Thanks for the video.
Lifesaver you are my friend, thanks so much :)
The best, the best, the best. I love it
Can we extract the data from a text file based on nth delimiter basically splitting a file based on the delimiter and also the size
Amazing-Excel
excellent sir
How about the middle string? What will be the formula to just extract
"Another Dir" removing the left and right characters
I also need this, please let us know
Really helpful tutorial. My only nag is that you have to know which instance the last delimiter is at and change the instance number. But what if all of your directory paths have a different number of "\" characters, and I want to tell Excel to "find me the last one?"
I tested this out using:
C:\Dir\Another Dir\Some File\Example.xlsx
C:\Dir\Another Dir\Some File.xlsx
C:\Dir\Another Dir.xlsx
And I got:
=FIND("~",SUBSTITUTE(A1,"\","~",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))
This will always find the final instance of the delimiter without having to know or count it. Thus, we have:
=RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1,"\","~",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))
Dragging this formula down column B will yield:
Example.xlsx
Some File.xlsx
Another Dir.xlsx
Same thing, if i want to get the left part?
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.
Kamesh, you can extract as much as you want! But, the formula might end up a bit interesting. Go ahead and ask this question in our forum on TeachExcel and include a description of exactly what you are trying to do and any progress that you made so far and we can get you a formula :) Forum: www.teachexcel.com/talk/microsoft-office?src=yt_comment
@@TeachExcel
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)&"~")))
Very helpful
How do I get a middle text like \Another Dir\ ?
What if I have to get "Another Dir"
That 'Swigle' above the Tab button and beside '1' button is called *Tilde*
Thank you!
what is excel formula to split value from AAAA+BB+CC to AAAA AABB and AACC
SRVKOL/236/22-23
SRVKOL/5389/22-23
SRVKOL/2/22-23
SRVKOL/58/22-23
I want to extract only numbers (i.e., 236, 5389, 2, 58) using formula. Please advise. Thanks
Hi friend! I tried the last formula but I can´t , or it doesnt work with "-" , this is the complete sentence : AP - hi how are you - window and i tried with this formula : =RIGHT(C1,LEN(C1)-FIND(" - ",SUBSTITUTE(C1," - "," ~ ",2))) and is no working pleaseee help me
How can you extract multiple occurrences? For example, I am trying to extract all occurrences of the word "Scale"? In this example, Methods: Cross-sectional descriptive survey of 447 US RNs using five reliable and validated measures of presenteeism: Stanford Presenteeism Scale (SPS), Job-Stress-Related Presenteesim Scale (JSRPS), Healthcare Productivity Scale (HPS), Nurses Work Functioning Questionnaire (NWFQ), and Health and Work Questionnaire (HWQ). The survey was evaluated using descriptive, exploratory, and confirmatory factor analysis.
What if I have multiple pieces of data between multiple delimiters? i.e., xxx.xxxxx.xxxxxxxx.xxx.x.xxxx.x and I want to get data out to cells without using the text to column feature, but rather using a formula, to extract each piece of data so xxx then in the next column xxxxx then xxxxxxxx then xxx then x then xxxx then x from left to right?
can you do this and pull the text string between 2 of the "\"...i.e I want to pull the "Another Dir" via formula
Monitum: 28 dec 2019 16:07:52: auto RSP issue~monitum: 10 jan 2020 14:18:47 : ~vibhanshu singh bharti: 10 jan 2020 17:02:32 : monitor not working properly~
Hello can you help me fot my project? I want to extract the first 2 words of the file name. And if the first word is numbers. I want to extract the next 2 words to it. Pls help. I can pay thru paypal.
It does not work for me ::(
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)&"~")))
Hey Great Video to extract the needed. However I feel in sharing the below add-on where in every time that we are selecting the number of "\" character in the formula used. We can replace that part by using "=RIGHT(G25,LEN(G25)-FIND("~",SUBSTITUTE(G25,"/","~",LEN(G25)-LEN(SUBSTITUTE(G25,"/","")))))".
your formula returns everything after the last occurrence, and that can be done with the shorter: =TRIM(RIGHT(SUBSTITUTE(G25,"/",REPT(" ",LEN(G25))),LEN(G25)))
i want to contact with you
Click the Contact link at the top of my website: TeachExcel.com
How extract from VIBHANSHU SINGH BHARTI from this column
Please help
+1