The course teaches text formulas and functions in Excel Worksheet and Power Query. The learning outcomes include joining items, using various text functions, fixing errors, creating dynamic messages, extracting data, and cleaning datasets. The course covers skills such as using TEXTJOIN, CONCAT, XLOOKUP, DOLLAR, FIXED, LEFT, RIGHT, FORMULATEXT, TRANSPOSE, SEARCH, FIND, FILTER, REPLACE, SUBSTITUTE, TRIM, LEN, and more. The teaching method involves practical examples and demonstrations. The intended audience for this course is individuals looking to enhance their Excel skills, particularly in text manipulation and data cleaning using Power Query.
Overview
Syllabus
) Introduction.
) Join three items with the Join Operator, Ampersand &.
) Why you use formulas rather than Flash Fill.
) TEXTJOIN function to join three items with .
) CONCAT function to join three items with.
) Extract data from cell with dash delimiter to use in lookup formula. Learn about TEXTBEFORE, TEXTAFTER and XLOOKUP functions..
) XLOOKUP Error: Data Mismatch and how to fix it. Convert Text Number to Number with math operation..
) XLOOKUP Error: Extra spaces. Discover issue with the LEH function to count characters in a cell..
) TEXTSPLIT function to split text to multiple cells with.
) DOLLAR function to create dynamic customer invoice message .
) FIXED function to create dynamic customer invoice message.
) TEXT function and Custom Number Formatting to create dynamic customer appointment message.
) Text Formulas and Spelling Errors..
) TEXT function and Custom Number Formatting to create dynamic customer loan due date message.
) TEXT function and Custom Number Formatting to create dynamic customer loan interest rate message.
) LEFT and RIGHT functions to extract data based on a fixed number of characters..
) FORMULATEXT and TRANSPOSE function to show formulas as text, a great model documentation tool..
) Lookup records in table based on partial test using the functions: SEARCH, FIND and FILTER..
) REPLACE function to replace text based on a fixed position..
) REPLACE function to insert text..
) SUBSTITUTE function to replace text based on a sub-text string..
) TRIM function to remove Spaces (Character 32).
) LEN function to count characters in cell.
) Remove Non-Breaking Spaces (Character 160) from text using the functions: TRIM, SUBSTITUTE, CHAR, CODE, MID, LEN, and SEQUENCE. .
) TEXTJOIN to join a range of cells with text using a delimiter..
) TEXTJOIN to create e-mail list.
) Look at Text File (Tab Delimited) to see non-printing characters for space and non-breaking space.
) Power Query to clean a data set using Power Query Text functions..
) Change Power Query default settings to avoid automatic change data step that adds data types.
) Rename steps in Power Query .
) Three examples of Split Text feature in Power Query, including editing M Code to rename fields.
) Remove spaces from start and end of a text string using Trim Feature.
) Remove extra spaces between words in Power Query using the Replace feature.
) Load Query as Only Create Connection.
) Edit Load To location after query has been loaded.
) Summary and Conclusions.
) Closing and Video Links.
Taught by
ExcelIsFun
