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