Text Formulas & Functions in Excel Worksheet and Power Query
Offered By: ExcelIsFun via YouTube
Course Description
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
Related Courses
Data Wrangling with MongoDBMongoDB via Udacity Data Science Essentials for SAP
OnSAP Academy via Independent Herramientas de la Inteligencia de Negocios
Galileo University via edX Digital Media Analytics: Using 'Listening Data'
Purdue University via FutureLearn Advanced Business Analytics
University of Colorado Boulder via Coursera