Главная
Study mode:
on
1
) Introduction
2
) Join three items with the Join Operator, Ampersand &
3
) Why you use formulas rather than Flash Fill
4
) TEXTJOIN function to join three items with
5
) CONCAT function to join three items with
6
) Extract data from cell with dash delimiter to use in lookup formula. Learn about TEXTBEFORE, TEXTAFTER and XLOOKUP functions.
7
) XLOOKUP Error: Data Mismatch and how to fix it. Convert Text Number to Number with math operation.
8
) XLOOKUP Error: Extra spaces. Discover issue with the LEH function to count characters in a cell.
9
) TEXTSPLIT function to split text to multiple cells with
10
) DOLLAR function to create dynamic customer invoice message
11
) FIXED function to create dynamic customer invoice message
12
) TEXT function and Custom Number Formatting to create dynamic customer appointment message
13
) Text Formulas and Spelling Errors.
14
) TEXT function and Custom Number Formatting to create dynamic customer loan due date message
15
) TEXT function and Custom Number Formatting to create dynamic customer loan interest rate message
16
) LEFT and RIGHT functions to extract data based on a fixed number of characters.
17
) FORMULATEXT and TRANSPOSE function to show formulas as text, a great model documentation tool.
18
) Lookup records in table based on partial test using the functions: SEARCH, FIND and FILTER.
19
) REPLACE function to replace text based on a fixed position.
20
) REPLACE function to insert text.
21
) SUBSTITUTE function to replace text based on a sub-text string.
22
) TRIM function to remove Spaces (Character 32)
23
) LEN function to count characters in cell
24
) Remove Non-Breaking Spaces (Character 160) from text using the functions: TRIM, SUBSTITUTE, CHAR, CODE, MID, LEN, and SEQUENCE.
25
) TEXTJOIN to join a range of cells with text using a delimiter.
26
) TEXTJOIN to create e-mail list
27
) Look at Text File (Tab Delimited) to see non-printing characters for space and non-breaking space
28
) Power Query to clean a data set using Power Query Text functions.
29
) Change Power Query default settings to avoid automatic change data step that adds data types
30
) Rename steps in Power Query
31
) Three examples of Split Text feature in Power Query, including editing M Code to rename fields
32
) Remove spaces from start and end of a text string using Trim Feature
33
) Remove extra spaces between words in Power Query using the Replace feature
34
) Load Query as Only Create Connection
35
) Edit Load To location after query has been loaded
36
) Summary and Conclusions
37
) Closing and Video Links
Description:
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.

Text Formulas & Functions in Excel Worksheet and Power Query

ExcelIsFun
Add to list
#Business #Business Software #Microsoft Office 365 #Microsoft Excel #Data Science #Data Analysis #Data Cleaning #Data Extraction #Business Intelligence #Power Query #Spreadsheets #XLOOKUP
0:00 / 0:00
1 of 37

) Introduction