Name: Text Manipulation Techniques in Excel
Brand: Aurora Training Advantage
Price: 219.00 USD
Availability: InStock

Question 1

How do the LEFT, MID, and RIGHT functions extract text in Excel?

Accepted Answer

LEFT, MID, and RIGHT are Excel's three core text extraction functions, each targeting a different portion of a text string. LEFT(text, num_chars) extracts characters from the beginning: =LEFT("Invoice-2024",7) returns 'Invoice'. RIGHT(text, num_chars) extracts from the end: =RIGHT("TX-78704",5) returns '78704'. MID(text, start_num, num_chars) extracts from any position: =MID("Account 4100 Revenue",9,4) returns '4100'. The FIND or SEARCH function is often combined with these to locate a delimiter dynamically rather than using a fixed position: =LEFT(A1,FIND("-",A1)-1) extracts everything before the first hyphen regardless of its position. For extracting account numbers, extracting first or last names, parsing codes, or splitting concatenated identifiers, these three functions are the primary tools. In Microsoft 365, the newer TEXTBEFORE and TEXTAFTER functions simplify extractions based on delimiters without requiring position calculations. TEXTSPLIT goes further, splitting a string into multiple cells based on a specified separator. For accounting professionals working with exported data where account numbers, names, and codes are combined in single columns, mastering LEFT/MID/RIGHT plus FIND/SEARCH is essential for parsing that data into usable separate fields. David Ringstrom, CPA, covers all these functions in Aurora Training Advantage's Excel Agility: Text Manipulation Techniques on-demand webinar.

Question 2

How do UPPER, LOWER, PROPER, and TRIM transform text in Excel?

Accepted Answer

Excel's text case and cleanup functions standardize inconsistently formatted text data. UPPER(text) converts all characters to uppercase—=UPPER("john smith") returns 'JOHN SMITH'. LOWER(text) converts all characters to lowercase—useful for standardizing email addresses or codes before comparison. PROPER(text) capitalizes the first letter of each word—=PROPER("JOHN SMITH") returns 'John Smith', the standard format for name fields. TRIM(text) removes leading spaces, trailing spaces, and reduces multiple internal spaces to single spaces—essential for cleaning data exported from databases or accounting systems that pad text fields. A common cleaning formula combines multiple functions: =PROPER(TRIM(A1)) standardizes both spacing and capitalization in one step. For VLOOKUP or XLOOKUP failures caused by invisible leading spaces, wrapping the lookup value in TRIM ensures a clean match: =XLOOKUP(TRIM(A1),lookup_range,return_range). CLEAN removes non-printing characters like line breaks and tab characters imported from other applications—often necessary for data pasted from websites or extracted from PDFs. For databases with mixed-case entries (a customer name entered as 'SMITH john' in one record and 'Smith, John' in another), these functions enable standardization before analysis. They are among the most frequently used text tools for accountants and data professionals covered in Aurora Training Advantage's Text Manipulation Techniques webinar.

Question 3

What are TEXTBEFORE, TEXTAFTER, and TEXTSPLIT in Excel and how do they simplify text parsing?

Accepted Answer

TEXTBEFORE, TEXTAFTER, and TEXTSPLIT are modern text parsing functions available in Microsoft 365 that dramatically simplify extractions that previously required complex nested LEFT/MID/FIND formulas. TEXTBEFORE(text, delimiter) returns everything before the first occurrence of the delimiter: =TEXTBEFORE("John Smith - Manager"," - ") returns 'John Smith'. TEXTAFTER(text, delimiter) returns everything after: =TEXTAFTER("John Smith - Manager"," - ") returns 'Manager'. Both functions support an instance_num argument for targeting the Nth occurrence of a delimiter, and a match_end argument for handling cases where the delimiter doesn't exist. TEXTSPLIT(text, col_delimiter, row_delimiter) splits a string into a spilled array of separate values: =TEXTSPLIT("Red,Blue,Green",",") returns a horizontal array containing Red, Blue, Green in separate cells. TEXTSPLIT can split both horizontally by column delimiter and vertically by row delimiter simultaneously, even handling irregular text structures. For accounting professionals parsing QuickBooks exports, address fields, product codes, or concatenated employee records into structured columns, these three functions replace multi-step formula chains with a single readable formula. They represent a significant advance in Excel's text manipulation capabilities available to Microsoft 365 subscribers, covered in depth in Aurora Training Advantage's Text Manipulation Techniques webinar with David Ringstrom, CPA.

Question 4

How does the TEXT function format numbers and dates as text strings in Excel?

Accepted Answer

The TEXT function converts a numeric value—including dates, times, and numbers—into a formatted text string using a custom format code. Its syntax is =TEXT(value, format_code). For dates: =TEXT(A1,"mmmm d, yyyy") converts a date serial number to a spelled-out format like 'January 15, 2024'. For numbers: =TEXT(1234567,"$#,##0.00") returns '$1,234,567.00' as text. TEXT is especially useful when combining formatted numbers or dates with other text strings: ="Report as of "&TEXT(TODAY(),"mmmm yyyy") produces 'Report as of March 2026' as a dynamic cell heading. Without TEXT, concatenating a date directly produces the date's serial number (like 46000) rather than the readable date. TEXT is also used for redacting sensitive numbers: =TEXT(A1,"###-##-"&TEXT(RIGHT(A1,4),"0000")) displays only the last four digits of a Social Security Number. The format codes used in TEXT follow the same syntax as custom number formats in Format Cells. One important note: TEXT output is a text string, not a number—the result cannot be used in arithmetic calculations. For purely display purposes in labels, headings, and concatenated messages, TEXT is the most flexible formatting tool in Excel's text function library, covered extensively in Aurora Training Advantage's Text Manipulation Techniques on-demand webinar.

Question 5

How does the Text to Columns wizard split data into separate columns in Excel?

Accepted Answer

Text to Columns (Data > Text to Columns) is Excel's built-in wizard for splitting a single column of combined data into multiple separate columns. The wizard has two modes: Delimited (splits at a specified character like comma, tab, semicolon, space, or a custom delimiter) and Fixed Width (splits at specified character positions). For Delimited data, step 2 lets you select the delimiter and preview the column splits. Step 3 lets you set the data type for each resulting column—particularly important for date columns that may need a specific date format specified to parse correctly. Text to Columns is a non-destructive one-time operation that outputs to the right of the source column—ensure adjacent columns are empty before running it to avoid overwriting existing data. Common applications include splitting full names into first and last name columns, separating street address from city/state/ZIP, parsing account code and description from a combined field, and fixing date columns imported as text (running Text to Columns on a text-formatted date column with the date type specified converts it to actual Excel dates). The TEXTSPLIT function in Microsoft 365 achieves the same result as a formula that spills dynamically—but Text to Columns remains the most accessible option for users on all Excel versions and those needing a quick one-time split without formulas.

Excel Agility: Text Manipulation Techniques

Purchase Options

Recorded Webinar Only

All Access Membership

David H. Ringstrom, CPA

CPE Credit

Frequently Asked Questions