Highline Excel 2016 Class 08: Text Formulas and Text Functions to Join and Extract Data

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

[md.ishtiakazim]

Done
This is the best text formula tutorial. Thanks Mike.

[advnturetim]

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

[amenaislam]

Thank you .
Convert text number to Date, its an amazing trick. Thanks again for sharing this amazing trick. :)

[pattyboggs]

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!

[idlevandal]

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))

[rockguitarist]

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.

[StoryBookJunction]

Hi sir,
you are really great person. i & many peoples learned many formulas & functions by your videos.
salute you sir...
Prahlad Choudhary

[shubhampawar]

Never seen text function in such detail ..thanks a lot 👍

[tommyoneill]

This is great...for years I've been using google to search the formula to extract text, now I know & understand it. Thanks Mike.

[codyt.]

This was the best Excel video I watched tonight!

[sabinezinkpielage]

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 ;-)

[jamesdiaz]

I`ve learned many things through this channel, Thanks!

[eenab]

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.

[Chamchijjige]

Thank you very much. Cant slepp to finish all your videos.

[marcel]

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.

[alaamaoula]

Omg!! THANK YOU!! I understand everything from you! thank you for making it easy for us to understand. Subscribed!

[bruhgamer-bdej]

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!!!

[sherryizzie]

TextJoin function, it is a smarter function than ampersand and concatenate:):) I am glad I pick up this function:)

[Luciano_mp]

Mike. Você é um excelente professor. Obrigado pelo seus ensinamentos. Parabéns.

[nagizadeorxan]

Great video, thank you for all your efforts!