Text Formulas & Functions in Excel Worksheet and Power Query

Overview

Save Big on Coursera Plus. 7,000+ courses at $160 off. Limited Time Only!

Grab it

Explore advanced text formulas and functions in Excel and Power Query through this comprehensive 34-minute video tutorial. Learn to join text using operators and functions like TEXTJOIN and CONCAT, extract data with TEXTBEFORE and TEXTAFTER, and master XLOOKUP for efficient data retrieval. Discover techniques for handling errors, splitting text, and creating dynamic messages using DOLLAR, FIXED, and TEXT functions. Dive into powerful text manipulation tools such as LEFT, RIGHT, REPLACE, and SUBSTITUTE. Gain insights on cleaning data sets with Power Query, including removing spaces and splitting text. Excel MVP Mike Girvin guides you through practical examples, ensuring you can apply these skills to real-world scenarios in Microsoft 365 Excel.

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.