Highline Excel 2016 Class 08: Text Formulas and Text Functions to Join and Extract Data
HTML-код
- Опубликовано: 15 сен 2024
- Download Files: people.highlin...
In this video learn about:
1. (00:12) Introduction
2. (00:46) Text Formulas: Ampersand to Join Items together
3. (01:38) Text Functions to extract partial text items from a larger text item.
4. (02:57) Extract State with LEFT function
5. (04:07) Extract Zip Code with RIGHT function
6. (04:59) Extract First Name with LEFT and SEARCH functions
7. (06:46) Extract Last Name with RIGHT, LEN and SEARCH functions
8. (08:50) Extract Product with LEFT and SEARCH functions
9. (10:07) Extract Region with MID and 3 SEARCH functions
10. (13:28) Extract Money Amount from end of description Field with RIGHT, LEN and SEARCH functions, and a MATH operation to Convert Text Number Back to a Number
11. (15:34) TRIM function to remove extra spaces.
12. (16:22) Create Serial Number Date from ISO Date using DATE, LEFT, MID and RIGHT functions.
13. (18:45) Create Serial Number Date from ISO Date using TEXT function, Custom Number Format and Math Operation to Convert Text Number back to a Number.
14. (22:00) TEXT function and Custom Number Formatting to create labels
15. (26:02) Text Formulas: TEXTJOIN Excel 2016 Function
16. (29:05) Summary
Done
This is the best text formula tutorial. Thanks Mike.
You are welcome!!!!
Never seen text function in such detail ..thanks a lot 👍
You are welcome a lot!!! : ) : )
Fantastic. Some powerful way to use text formulas in this video.
The 'Textjoin' function for an email list was excellent.
Thanks so much once again
You are welcome!!
It is amazing what all Excel can do! This is my 'go to' site when I need to figure out how to do something or just to learn something new. You are a great teacher! Thank you for making your channel available to us mortals!
Glad it was helpful!
You are an exceptional instructor!
Thank you for the kind words and for your support with comment and Thumbs Up : )
This training is exceptional
Glad it helps, Bharat!!!!
@@excelisfun3903 i ve always encountered these functions up in the ribbon, never knew they could be so useful. I ve tried hard reading the documentation, but was unable to comprehend the way u explained each topic. You are an amazing teacher. Thanks a ton man!
@@yt_bharat You are welcome a ton! Almost any topic your need, I have a video fro the collection og 3000+ videos that I have posted over the last 12 years. Need a topic, just ask : )
@@yt_bharat Thanks for supporting my efforts with your thumbs ups and comments!!!!
Can't say enough about how much I enjoy these online tutorials, the world needs more people like you!!!!
I tried this:
Instead of adding +0 to the TEXT(ISO date, "0000-00-00") at 21:00 you can wrap it in the DATEVALUE function....
=DATEVALUE(TEXT(ISO date, "0000-00-00))
Nice DATEVALUE!!! I am glad that the videos help! You can help me with a Sub, and comment and Thumbs Up on each video that you watch : )
I totally agree when you said the world needs more people like him :)
I`ve learned many things through this channel, Thanks!
I am glad that you have learned many things at this channel!!! Thanks for your support with Thumbs Up and comment ion each video that you watch! It helps the channel to get to even more eager Excel Learners for free : )
Sure
: )
Mike, your vids helped me get a F.A. position and then an F.A. position. Own your books and support you by spreading the word everywhere I go! Cannot thank you enough for helping me in my career.
You are welcome! That is why I post! I love to hear when people use the videos and work hard, learn Excel and do awesome : ) Thank you for your support with Sub and Thumbs Up on each vide that you watch : )
Thank you .
Convert text number to Date, its an amazing trick. Thanks again for sharing this amazing trick. :)
You are welcome!
You are a true guru! 💐💐💐💐!
Always glad to help, the Aka!!!!
Hi sir,
you are really great person. i & many peoples learned many formulas & functions by your videos.
salute you sir...
Prahlad Choudhary
You are welcome, PR!!!!
This is great...for years I've been using google to search the formula to extract text, now I know & understand it. Thanks Mike.
This was the best Excel video I watched tonight!
Glad you liked it, Cody T.!!!
Thank you very much. Cant slepp to finish all your videos.
You are very welcome!!!!!
Omg!! THANK YOU!! I understand everything from you! thank you for making it easy for us to understand. Subscribed!
You are welcome, Alaa!!! Thanks for your support with your comment, Thumbs Ups and Sub : )
You are an amazing teacher, thanks
Thank you very much for the very easy to understand, step by step tutorial. It really helped me to understand way faster. Keep it up!!!
You are welcome!
Great video, thank you for all your efforts!
You are welcome!!!!
TextJoin function, it is a smarter function than ampersand and concatenate:):) I am glad I pick up this function:)
=TEXTJOIN(" ",,{"Thanks","for","stopping","by,","sherry izzie"})
You are amazing sir!
Thank U Teacher.
You are welcome, Master Jack!!!!
First of all: THANK YOU for your excellent videos!!!
I just want to add an easy way out if you want to concatenate 3 cells where 1 or more might be empty: = TRIM(A1&" "&B1&" "&C1), if you are not yet on Excel 2016 or later and cannot use the TEXTJOIN function yet ;-)
MIND BLOWING 😵😵😵
TEXTJOIN nice. I just joined the Insider program on my Mac 365 subscription then after a 1.5gb update I now have TEXTJOIN. So Happy.
thank you this was amazing
That was awesome Sir! Thank you
Glad it was awesome !!! Thanks for the Sub, comment and Thumbs Up on each video that you watch : )
Mike. Você é um excelente professor. Obrigado pelo seus ensinamentos. Parabéns.
You are welcome! I am glad that the videos help! : )
You can thank me by clicking Thumbs Up on each video that you watch! : )
Thanks for this easy to follow tutorials
Thank you, so very much!!Peter H.
You are welcome very much! Thanks for the Sub, comment and Thumbs Up!! : )
Great lesson.
I do not have the "TextJoin" but a workaround to join the array of text at 27:54 is to use "SumProduct" put the array as an argument click F9 to show the array, put a space at the beginning of the formula, copy the joined text, then use REPLACE the quotation mark with nothing.
Amazing!
Such a wild video 😂😍😍
This is one of the best videos .. so much to learn ♥️
Glad you learned so much, Shubham!!!!
@@excelisfun please make some videos on MIS reporting too ♥️
Thanks again mike!
You are welcome again!
Thank You, helpful, and easy, :)
You are welcome, Naturo! Thanks for the support with your comment, Thumbs Up and Sub : )
@@excelisfun
Yes for sure. My Pleasure.
@@ruroshinzynaruto Go Team!!!!!
Awesome sir, God bless you.
Glad you like it, Khan!!! Thank you for the support with your comment, Thumbs Up and Sub : )
Thanks
You are welcome, clifford : )
I should have noted that the number of spaces changes as you go down the list of names. There may be 4 spaces in one name and 2 or 1 space in another. The cell may only contain a first and last name or it may contain titles and first and last names. For example: Mr and Mrs Arnold Smith or just Jane Smith.
not aware that ampersand is also the other way of concatenate function.
That was really helpful
Glad it helped! Thanks for the support with your comment, Thumbs Up and Subscription! Many more videos to come.
I never used search, always make (Find) work for me. :)
SEARCH is not case sensitive, FIND is case sensitive and SEARCH can be entered with two keystrokes (S and Tab) whereas FIND takes four (F, I, N, Tab).
Thanks a lotttttttttttt
+Mohamed Chakroun You are welcome a looooottttttttt!
So helpfull..
This was just excellent. This video was super-helpful to me. Lots of info, and clearly stated. Thank you so much.
I have something that I have been struggling with, so I'm just going to ask... ;)
If I interrupt an autofill series with subheaders in merged cells, is there any way to get it to ignore those merged cells and carry on after them? For instance, to have a date series that will continue on down the page, even when I stick in the Month name at the top? Or do I need to re-start the series manually each time? I have a couple oddball things like this that I was wanting to do to create a planner for myself, and I'm just wondering if it is even possible?
Thanks so much! What if the ":" was another "/"? How do you tell Excel ignore the first "/" and calculate from the second "/"? For example, instead of Quad / West: 399.95, it is Quad / West / 399.95. I want to get the text "Quad / West" from the string and ignore the rest.
Hi, thanks for the video. However, I have to extract the last name in a cell that contains several spaces such as - Mr and Mrs John Smith. I tried the example you gave with the first and last name and it works. However, when I tried it with my Mr and Mrs John Smith example, I got : and Mrs John Smith. I would seriously appreciate the help.
I was hoping there is an easy way to tell the formula to extract the last name only after the LAST space. Thanks again.
Can you please share us the excel files once again because the attached file in the description is not working.
Thanks Mike. -WRH
+krn14242 , You are welcome, WRH!!!!
Hi I'ld like to ask if there's a way to tell excel to take the text in A1, find it in A2 and replace it with something else. For example. if A1 has the word "change", A2 as the sentence "don't change this" then I tell excel that I want to replace the word "change=@" so that A2 with then look like "don't @ this". I would then take this formula to repeat it on the two columns with sentences and word.
Mike.... At 22:15 video.. .. If i want the font color to be red for "2:00 AM"... And use fromat argument as "[Red] h:mm AM/PM"... This doest show 2 pm in red color... Whyy????..., is there a way to change font color using text
Hi, may i have your answer please, i know u are proficient in ME i need to have a formula of days that update days of the months only, for example, I have 12 pages first page with January2020 second page with February2020 and so on, i need the days only updated with the days of each day but when it reach the end of the month it stop and start in the next page with the next month, i hope you understand me.
Hi Mike
This Vidoe is really amazing But i have query In excel sheet Text(1) in row cell D5:G5 we write anything and hit tab or enter then cell automatically colored but leave as empty then press tab or return key then cell does not color
Please let me know how we do this?
Hi mike,
hope You are doing well
I had asked a query. trick use in this query is really amazing for me and i want to learn this
Please let me know how can i do this this.
Hi mike,
You applied condition formatting rule
new rule Home tab>condition formatting>new rule>format all cells based on their value
really amazing formatting rule
I love it.
Thanks
What type of microphone are you using? Your voice is clear, but sometimes sounds like you are under water. Is there a gain setting or level??
+krn14242 , Riiiiight... I have had trouble with the sound levels in my videos and so that is the underwater sound... :( I will fix it soon, I hope.
+krn14242 Can you tell me a minute mark where you decipher the under water sound? i would be curious where you hear it, because it might be because of editing or sound level adjustments...
+ExcelIsFun It was very close to the beginning where I noticed... Maybe 0:20 - 0:24 seconds in. Not bad by any means, just detected a twinge here and there and was curious if your audio was "hot".
+krn14242 , yes, that was because of editing different sections of videos together...
How to remove third space in a sentence like "remove the third space in sentence" will look like as "remove the thirdspace in sentence".
hello,just a questions is text join function available in open office. i use open office so wonder if itpresent.thanks
+Juwon Adejuwon I don't know. I have never used Open Office... :(
Why not just use query?
I want to make Task manager dashboard using Excel can you help me
Here is video later in this series:
ruclips.net/video/hYPwX_CfYv4/видео.html
Sir, I want dashboard for Tasks & to do list in excel, pls help me
mike, how can i extrapolate, just the surname from a whole bunch of email addresses which have different lengths? An email address example uses this format in a single cell: FirstinitialSurname@host.com. can you help?