Wow! In all the years I’ve been listening to “how to” teaching videos regarding text manipulation, I have NEVER heard anything so simply and expertly explained as this example. You included so many breakdowns of the entire formula that made a tremendous difference in understanding how these multiple function (imbedded) formulas work. And I learned that the end of the formula goes first and works it’s way back to the first formula, which in and of itself is a tremendous help. You’ve made a indefinite subscriber out of me. With sincere thanks.
THERE ARE LOADS OF VIDEO on this topic and i watched more than 5, and clearly, you are the one explained it the best! Thanks for break it down and validate it on a granular level.
Finally the explanation I was looking for. Not the correct whole formula, but the way of thinking. I wanted the text from the right of the dot and left before last space. (translate google)
AE12= 115. Text and multiple variable spaces continued with 15.0 km Outcome: Text and multiple variable spaces continue with = TRIM (MID (AE12; FIND ("."; AE12) +2; FIND ("~"; SUBSTITUTE (AE12, ""; "~"; LEN (AE12) -LEN (SUBSTITUTE (AE12, ""; "")) - 1)) - (FIND ("."; AE12) +2))) Thanks
I have seen many videos so I really appreciate all whoever try to make other's life easier but I found you very much helpful. Great explanation! Well done my friend!
Thanks, this tutorial is very helpful. I've struggled for a long time to find the appropriate code that will do a specific task in my VBA project. Although, I did not use directly the command "Find" because it is unacceptable to VBA. I used "Instr" instead. What helped me solved my issue is the IDEA you have presented!!! THANK YOU VERY MUCH for your shared information. I already subscribed and liked your post.
This tutorial was quite helpful, I enjoyed watching it and practising along as you really did put effort to explain the magic "why" in Excel. I watched many tutorials on this topic, but here I got really what searched for simple explanation. Thank you.
situation: 1.- Each week I Get list in Excel to update in a Data Base (not excel), I use paste and copy 2.- a lot of times, the list has about 7 names inside(They are Variable), I need to separate all of them 3. It would be nice to do it, by formula (sight) The best way for me do to it : 1.-Text to column, separate by space 2.-Automate everything using macros What do you think Thank You You are the best in explaining !
that's great. thank you. what if we dont know how many delimiters we do not know (for instance in a1 we have 10, in a2 we have 15 delimiters) what we know is we want to get the text after the 7th delimeter from the end. for 2 days I am trying to get it but could not achive. can you advise? thanks again
Hi, i have a list of values on another sheet like 1-9. When i display the values from the first row and drag it down, it displays the values from 1-9 but after that the area is empty. Can you tell me how i have to write the function so it keeps repeating after the last value? Thx.
Dude do you have a video on using if function to return a number from a letter. So A=1 B=2 C=3 and so on. I want to enter a word and have excel automatically calculate the value of the word? Thanks
how would I change the formula to extract everything to the LEFT of the BAR/LINE that I substituted my spaces for? I tried to simply replace RIGHT with Left but It only returned the first name and not the first and last name that I need. please help!! :)
Hey TeachExcel, I modified one of your formulas to display everything before the first comma. (In my case the text is separated by commas). Below is my formula, but some of since some my fields don't have a comma, how would I edit this formula to display the original cell value if a comma isn't found? =IFERROR(LEFT(D1,FIND(",",D1)-1),"")
Just encase it in an IF statement that checks for that first. If you ask on our forum and provide some sample values it will be easier to give you better help, including code. www.teachexcel.com/talk/microsoft-office?src=yt_comment
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.
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)&"~")))
Well the last formula: How can this work: The last function "Substitute" has one argument less than it supposed to? It did not work when I tried te run it; the message keeps coming saying the formula misses an argument.
Hi david, just ask in our forum and provide a sample value or spreadsheet and it is MUCH easier to help - I can usually not respond to comments on older videos due to the quantity of them. For this question, provide a more specific example of how you want to extract it and if the value is just text that looks like a date or an actual date. How the date is stored will determine how to extract what you need. Forum: www.teachexcel.com/talk/microsoft-office?src=yt_comment
Wow! In all the years I’ve been listening to “how to” teaching videos regarding text manipulation, I have NEVER heard anything so simply and expertly explained as this example.
You included so many breakdowns of the entire formula that made a tremendous difference in understanding how these multiple function (imbedded) formulas work. And I learned that the end of the formula goes first and works it’s way back to the first formula, which in and of itself is a tremendous help. You’ve made a indefinite subscriber out of me. With sincere thanks.
yes I 100% agree with you. This video is worth gold.
I have watched many other videos regrading to extract data/spilt text/data, you are the best one I think to explain soooooooo clearly!!
Excellent! The multi-separation formulas are brilliant - thanks for that!
You are very welcome Mr. Lord! Glad you like it :)
THERE ARE LOADS OF VIDEO on this topic and i watched more than 5, and clearly, you are the one explained it the best! Thanks for break it down and validate it on a granular level.
Finally the explanation I was looking for. Not the correct whole formula, but the way of thinking. I wanted the text from the right of the dot and left before last space.
(translate google)
AE12= 115. Text and multiple variable spaces continued with 15.0 km
Outcome: Text and multiple variable spaces continue with
= TRIM (MID (AE12;
FIND ("."; AE12) +2;
FIND ("~"; SUBSTITUTE (AE12, ""; "~"; LEN (AE12) -LEN (SUBSTITUTE (AE12, ""; "")) - 1))
- (FIND ("."; AE12) +2)))
Thanks
I saw a lot of videos for this issue but i can tell this is the easiest and found what I wanted bcus of this video thank you so much❤
I have seen many videos so I really appreciate all whoever try to make other's life easier but I found you very much helpful. Great explanation! Well done my friend!
Thanks for explaining this in English logically!
You're very welcome! I'm glad you think so!)
Thanks, this tutorial is very helpful. I've struggled for a long time to find the appropriate code that will do a specific task in my VBA project. Although, I did not use directly the command "Find" because it is unacceptable to VBA. I used "Instr" instead. What helped me solved my issue is the IDEA you have presented!!! THANK YOU VERY MUCH for your shared information. I already subscribed and liked your post.
This tutorial was quite helpful, I enjoyed watching it and practising along as you really did put effort to explain the magic "why" in Excel. I watched many tutorials on this topic, but here I got really what searched for simple explanation. Thank you.
Thanks, you make it easy as you divided the long formula into pieces.
You're welcome! I'm glad that helped; I wasn't sure how easy it would be to follow as that last formula is pretty crazy.
Excellent explanation with good examples. I’m saving this. Thank you!
Awesome. This video helped me so much.
Thanks, well explained and without the distracting music etc. Well played 👏🏻
Very very useful and interesting tutorial. Extremely well explained. Big thanks!
You're welcome! Glad it helped :)
you should deserve more subscribers
Very well explained. Thank you
I have really liked it. Thank You!
situation:
1.- Each week I Get list in Excel to update in a Data Base (not excel), I use paste and copy
2.- a lot of times, the list has about 7 names inside(They are Variable), I need to separate all of them
3. It would be nice to do it, by formula (sight)
The best way for me do to it :
1.-Text to column, separate by space
2.-Automate everything using macros
What do you think
Thank You
You are the best in explaining !
Excellent video... Thanks!
Thank You It is Great Explanation
Stunning Video!!!
Thanks :)
Brilliant content! Thanks a lot :-)
Thanks Marco :) You're very welcome!
Amazing tutorial
Great Job! Thank you so much!
Great work, thank you
that's great. thank you.
what if we dont know how many delimiters we do not know (for instance in a1 we have 10, in a2 we have 15 delimiters)
what we know is we want to get the text after the 7th delimeter from the end.
for 2 days I am trying to get it but could not achive.
can you advise?
thanks again
Genius, thanks a lot
You're welcome! :)
Hello, do you have a video that explains how to get the "C" on A6 using the formula?
perfect video
Hi,
i have a list of values on another sheet like 1-9.
When i display the values from the first row and drag it down, it displays the values from 1-9 but after that the area is empty.
Can you tell me how i have to write the function so it keeps repeating after the last value?
Thx.
thank you very much for this great video
You're welcome!
Dude do you have a video on using if function to return a number from a letter. So A=1 B=2 C=3 and so on. I want to enter a word and have excel automatically calculate the value of the word? Thanks
how would I change the formula to extract everything to the LEFT of the BAR/LINE that I substituted my spaces for? I tried to simply replace RIGHT with Left but It only returned the first name and not the first and last name that I need. please help!! :)
THANKS ALOT
Thanks a lot
can you please tell me how we canFind 80-70percent similar sentences from excel.
Not able to register in your teachexcel and not able to download the materials
Hi Ken, not sure what the issue is, if you send me a message via the Contact page on TeachExcel.com, I can better troubleshoot it.
Hello, Can we do like AB-B=A in excel ?
Hi, could please tell how i can get everything after the second slash for example (19000-4444-55555--5050-B01889-) ?
Hey TeachExcel,
I modified one of your formulas to display everything before the first comma. (In my case the text is separated by commas). Below is my formula, but some of since some my fields don't have a comma, how would I edit this formula to display the original cell value if a comma isn't found?
=IFERROR(LEFT(D1,FIND(",",D1)-1),"")
Just encase it in an IF statement that checks for that first. If you ask on our forum and provide some sample values it will be easier to give you better help, including code. www.teachexcel.com/talk/microsoft-office?src=yt_comment
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.
thanks bro
Cool😍😍
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)&"~")))
مرسی
Well the last formula: How can this work: The last function "Substitute" has one argument less than it supposed to? It did not work when I tried te run it; the message keeps coming saying the formula misses an argument.
When I press F9 it takes me to a windows menu
what about dates ? What extracting a date eg 29-08-20. just extract the entire date
Hi david, just ask in our forum and provide a sample value or spreadsheet and it is MUCH easier to help - I can usually not respond to comments on older videos due to the quantity of them. For this question, provide a more specific example of how you want to extract it and if the value is just text that looks like a date or an actual date. How the date is stored will determine how to extract what you need.
Forum: www.teachexcel.com/talk/microsoft-office?src=yt_comment
WoW