excelisfun | Text Formulas & Functions in Excel Worksheet and Power Query - 365 MECS 06 @excelisfun | Uploaded 2 years ago | Updated 1 day ago
Download Excel file: https://excelisfun.net/files/06-M365ExcelClass.xlsx
Download pdf notes: https://excelisfun.net/files/06-M365ExcelClass.pdf
Text file for Power Query example (right-click to download): https://excelisfun.net/files/06-M365ExcelClassTextFile.txt
This video is a comprehensive lesson in text formulas and functions in the Excel Worksheet and Power Query.
This full free Microsoft 365 Excel & Power BI class is taught by Excel MVP and Highline College Professor and can be found here: https://www.youtube.com/playlist?list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW
Topics in video:
1. (00:00) Introduction
2. (00:55) Join three items with the Join Operator, Ampersand &
3. (01:57) Why you use formulas rather than Flash Fill
4. (02:18) TEXTJOIN function to join three items with
5. (03:02) CONCAT function to join three items with
6. (03:16) Extract data from cell with dash delimiter to use in lookup formula. Learn about TEXTBEFORE, TEXTAFTER and XLOOKUP functions.
7. (04:43) XLOOKUP Error: Data Mismatch and how to fix it. Convert Text Number to Number with math operation.
8. (08:16) XLOOKUP Error: Extra spaces. Discover issue with the LEH function to count characters in a cell.
9. (09:09) TEXTSPLIT function to split text to multiple cells with
10. (12:05) DOLLAR function to create dynamic customer invoice message
11. (13:19) FIXED function to create dynamic customer invoice message
12. (13:51) TEXT function and Custom Number Formatting to create dynamic customer appointment message
13. (13:55) Text Formulas and Spelling Errors.
14. (15:33) TEXT function and Custom Number Formatting to create dynamic customer loan due date message
15. (15:48) TEXT function and Custom Number Formatting to create dynamic customer loan interest rate message
16. (16:06) LEFT and RIGHT functions to extract data based on a fixed number of characters.
17. (16:39) FORMULATEXT and TRANSPOSE function to show formulas as text, a great model documentation tool.
18. (17:10) Lookup records in table based on partial test using the functions: SEARCH, FIND and FILTER.
19. (19:10) REPLACE function to replace text based on a fixed position.
20. (20:05) REPLACE function to insert text.
21. (20:34) SUBSTITUTE function to replace text based on a sub-text string.
22. (21:05) TRIM function to remove Spaces (Character 32)
23. (21:33) LEN function to count characters in cell
24. (21:51) Remove Non-Breaking Spaces (Character 160) from text using the functions: TRIM, SUBSTITUTE, CHAR, CODE, MID, LEN, and SEQUENCE.
25. (24:43) TEXTJOIN to join a range of cells with text using a delimiter.
26. (25:28) TEXTJOIN to create e-mail list
27. (26:40) Look at Text File (Tab Delimited) to see non-printing characters for space and non-breaking space
28. (27:29) Power Query to clean a data set using Power Query Text functions.
29. (28:00) Change Power Query default settings to avoid automatic change data step that adds data types
30. (29:00) Rename steps in Power Query
31. (29:20) Three examples of Split Text feature in Power Query, including editing M Code to rename fields
32. (31:50) Remove spaces from start and end of a text string using Trim Feature
33. (32:13) Remove extra spaces between words in Power Query using the Replace feature
34. (33:20) Load Query as Only Create Connection
35. (33:40) Edit Load To location after query has been loaded
36.
37. (33:56) Summary and Conclusions
38. (34:05) Closing and Video Links
Song in video: Rock Intro 3 by Audionautix is licensed under a Creative Commons Attribution 4.0 license. https://creativecommons.org/licenses/by/4.0/ . Artist: http://audionautix.com/
#MikeGirvin
#excelisfun
#MikeexcelisfunGirvin
#Microsoft365Excel
#MECS06
#Microsoft365ExcelCompleteStory
#ExcelTextFunctions
#JoinOperator
#Ampersand
#TEXTJOIN
#TEXTBEFORE
#TEXTAFTER
#XLOOKUP
#ErrorDataMismatch
#ErrorExtraspaces
#TEXTSPLIT
#DOLLARfunction
#FIXEDfunction
#TEXTfunction
#CustomNumberFormatting
#REPLACE
#SUBSTITUTE
#PowerQuerytocleanadataset
#PowerQueryTextfunctions
#EditLoadTolocation
#OnlyCreateConnection
#FreeClass
#FreePowerBIClass
#excelformulas
#excelformulas
#exceltricks
#textsplit
#textfunction
#excel
#powerquery
Download Excel file: https://excelisfun.net/files/06-M365ExcelClass.xlsx
Download pdf notes: https://excelisfun.net/files/06-M365ExcelClass.pdf
Text file for Power Query example (right-click to download): https://excelisfun.net/files/06-M365ExcelClassTextFile.txt
This video is a comprehensive lesson in text formulas and functions in the Excel Worksheet and Power Query.
This full free Microsoft 365 Excel & Power BI class is taught by Excel MVP and Highline College Professor and can be found here: https://www.youtube.com/playlist?list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW
Topics in video:
1. (00:00) Introduction
2. (00:55) Join three items with the Join Operator, Ampersand &
3. (01:57) Why you use formulas rather than Flash Fill
4. (02:18) TEXTJOIN function to join three items with
5. (03:02) CONCAT function to join three items with
6. (03:16) Extract data from cell with dash delimiter to use in lookup formula. Learn about TEXTBEFORE, TEXTAFTER and XLOOKUP functions.
7. (04:43) XLOOKUP Error: Data Mismatch and how to fix it. Convert Text Number to Number with math operation.
8. (08:16) XLOOKUP Error: Extra spaces. Discover issue with the LEH function to count characters in a cell.
9. (09:09) TEXTSPLIT function to split text to multiple cells with
10. (12:05) DOLLAR function to create dynamic customer invoice message
11. (13:19) FIXED function to create dynamic customer invoice message
12. (13:51) TEXT function and Custom Number Formatting to create dynamic customer appointment message
13. (13:55) Text Formulas and Spelling Errors.
14. (15:33) TEXT function and Custom Number Formatting to create dynamic customer loan due date message
15. (15:48) TEXT function and Custom Number Formatting to create dynamic customer loan interest rate message
16. (16:06) LEFT and RIGHT functions to extract data based on a fixed number of characters.
17. (16:39) FORMULATEXT and TRANSPOSE function to show formulas as text, a great model documentation tool.
18. (17:10) Lookup records in table based on partial test using the functions: SEARCH, FIND and FILTER.
19. (19:10) REPLACE function to replace text based on a fixed position.
20. (20:05) REPLACE function to insert text.
21. (20:34) SUBSTITUTE function to replace text based on a sub-text string.
22. (21:05) TRIM function to remove Spaces (Character 32)
23. (21:33) LEN function to count characters in cell
24. (21:51) Remove Non-Breaking Spaces (Character 160) from text using the functions: TRIM, SUBSTITUTE, CHAR, CODE, MID, LEN, and SEQUENCE.
25. (24:43) TEXTJOIN to join a range of cells with text using a delimiter.
26. (25:28) TEXTJOIN to create e-mail list
27. (26:40) Look at Text File (Tab Delimited) to see non-printing characters for space and non-breaking space
28. (27:29) Power Query to clean a data set using Power Query Text functions.
29. (28:00) Change Power Query default settings to avoid automatic change data step that adds data types
30. (29:00) Rename steps in Power Query
31. (29:20) Three examples of Split Text feature in Power Query, including editing M Code to rename fields
32. (31:50) Remove spaces from start and end of a text string using Trim Feature
33. (32:13) Remove extra spaces between words in Power Query using the Replace feature
34. (33:20) Load Query as Only Create Connection
35. (33:40) Edit Load To location after query has been loaded
36.
37. (33:56) Summary and Conclusions
38. (34:05) Closing and Video Links
Song in video: Rock Intro 3 by Audionautix is licensed under a Creative Commons Attribution 4.0 license. https://creativecommons.org/licenses/by/4.0/ . Artist: http://audionautix.com/
#MikeGirvin
#excelisfun
#MikeexcelisfunGirvin
#Microsoft365Excel
#MECS06
#Microsoft365ExcelCompleteStory
#ExcelTextFunctions
#JoinOperator
#Ampersand
#TEXTJOIN
#TEXTBEFORE
#TEXTAFTER
#XLOOKUP
#ErrorDataMismatch
#ErrorExtraspaces
#TEXTSPLIT
#DOLLARfunction
#FIXEDfunction
#TEXTfunction
#CustomNumberFormatting
#REPLACE
#SUBSTITUTE
#PowerQuerytocleanadataset
#PowerQueryTextfunctions
#EditLoadTolocation
#OnlyCreateConnection
#FreeClass
#FreePowerBIClass
#excelformulas
#excelformulas
#exceltricks
#textsplit
#textfunction
#excel
#powerquery
