Download Files:
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


Xem thêm bài viết:

28 thoughts on “Highline Excel 2016 Class 08: Text Formulas and Text Functions to Join and Extract Data”

  • Tommy O'Neill says:

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

  • Megan Watchorn says:

    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.

  • 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.

  • 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.

  • 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?

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

  • 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.

  • 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.

  • rockguitarist8907 says:

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>