Text Manipulation Techniques in Excel

In this presentation, author and Excel expert David H. Ringstrom, CPA, will delve into a comprehensive exploration of Microsoft Excel's powerful text manipulation tools and functions. He'll cover a wide range of topics, from mastering the LEFT/MID/RIGHT functions for precise text extraction to transforming text case with the UPPER/LOWER/PROPER functions. Discover the time-saving capabilities of the new TEXTBEFORE/TEXTAFTER and TEXTSPLIT functions in Microsoft 365. Learn how to efficiently unwanted characters by using the CLEAN and TRIM functions. Dive into the intricacies of the Text to Columns Feature, where you'll uncover techniques for handling addresses, dates, and names with ease. Additionally, explore advanced features like combining text with the CONCAT function and having Excel read text aloud to you by way of the Text to Speech Command.

David is the author of “Microsoft Excel 365 for Dummies”, “Exploring Microsoft Excel’s Hidden Treasures”, and has written or co-authored six other books. He demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Excel for Microsoft 365. David draws your attention to any differences in Excel 2021, 2019 or 2016 during the presentation and in his detailed handouts. The handouts include an Excel workbook with most of the examples he uses during his demonstrations.

Excel for Microsoft 365 is a subscription-based product that receives periodic feature updates. Conversely, perpetually licensed versions have year numbers in their names and do not receive any feature updates.

Who Should Attend
Professionals seeking to use Microsoft Excel more effectively.

Topics Covered

• Understanding when you might wish to use ISERROR or ISNA instead of IFERROR
• When Text to Columns won’t suffice, use the LEFT, MID, and RIGHT functions to extract text from a cell
• Employing the Text to Columns feature to transform text-based dates into numeric values that Excel recognizes as dates
• Using the TEXT function to force lookup values to match text-based table arrays
• Extracting data from pictures by way of the From Picture command
• Transforming dates and numbers into various formats without retyping by way of custom number formats
• Transforming text by way of Excel’s UPPER, LOWER, PROPER, and TRIM functions
• Using the CLEAN and TRIM functions to eliminate non-printing characters, such as tabs, carriage returns, and spaces, in your data that can trigger #VALUE! and other errors
• Splitting text into multiple cells based up on a separator that you specify with the TEXTSPLIT function
• Combining words or other text together by concatenating
• Using Text to Columns in any version of Excel to quickly separate city, state, and ZIP code into separate columns
• Separating first/last names into two columns without using formulas or retyping

Learning Objectives

• Recall where to restore the Track Changes command within the Quick Access Toolbar customization screen
• State what the FORMULATEXT displays when you reference a cell that does not contain a formula
• State which versions of Excel offer the Inquire add-in

Level
Intermediate

Instructional Method
Group: Internet-based

NASBA Field of Study
Specialized Knowledge and Applications (2 hours)

Program Prerequisites
Prior experience with Microsoft Excel is recommended.

Advance Preparation
None