Chapter 33: Latest Text Functions in Excel 365

Introduction to Latest Text Functions

Excel 365 introduces several new text functions that make it easier to handle and analyze text data. These functions are designed to simplify complex text operations and improve productivity. Here’s a detailed guide on the latest text functions available in Excel 365:

1. TEXTBEFORE Function

• Description: Extracts a substring from the beginning of a text string, up to a specified delimiter.
• Syntax: =TEXTBEFORE(text, delimiter, [instance_num])
• Example: =TEXTBEFORE(“Apple-Banana-Cherry”, “-“, 1) returns “Apple”. This function extracts text before the first instance of the delimiter “-“.

2. TEXTAFTER Function

• Description: Extracts a substring from the end of a text string, starting after a specified delimiter.
• Syntax: =TEXTAFTER(text, delimiter, [instance_num])
• Example: =TEXTAFTER(“Apple-Banana-Cherry”, “-“, 1) returns “Banana-Cherry”. This function extracts text after the first instance of the delimiter “-“.

3. TEXTSPLIT Function

• Description: Splits text into an array of substrings based on a specified delimiter.
• Syntax: =TEXTSPLIT(text, [delimiter], [column_delimiter])
• Example: =TEXTSPLIT(“Apple,Banana,Cherry”, “,”) returns {“Apple”, “Banana”, “Cherry”}. This function splits the text string into individual elements based on the comma delimiter.

4. TEXTJOIN Function

• Description: Joins multiple text strings into one string with a specified delimiter. This function is similar to older versions but with enhanced features.
• Syntax: =TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
• Example: =TEXTJOIN(” “, TRUE, “Hello”, “World”, “Excel”) returns “Hello World Excel”. This function concatenates text strings with a space delimiter, ignoring empty strings.

Detailed Usage Examples

TEXTBEFORE Example:

• Scenario: You have a list of email addresses in the format “username@domain.com” and you want to extract just the username.
• Formula: =TEXTBEFORE(A1, “@”)
• Result: If A1 contains “john.doe@example.com”, the formula returns “john.doe”.

TEXTAFTER Example:

• Scenario: You have a list of email addresses in the format “username@domain.com” and you want to extract just the domain.
• Formula: =TEXTAFTER(A1, “@”)
• Result: If A1 contains “john.doe@example.com”, the formula returns “example.com”.

TEXTSPLIT Example:

• Scenario: You have a cell containing a list of items separated by commas, such as “Apple,Banana,Cherry”, and you want to split this into individual items.
• Formula: =TEXTSPLIT(A1, “,”)
• Result: If A1 contains “Apple,Banana,Cherry”, the formula returns {“Apple”, “Banana”, “Cherry”}.

TEXTJOIN Example:

• Scenario: You have a list of names in separate cells and you want to combine them into a single cell with a comma separator.
• Formula: =TEXTJOIN(“, “, TRUE, A1:A3)
• Result: If A1

contains “John”, “Jane”, “Doe”, the formula returns “John, Jane, Doe”.

Practice Assignment

1. Text Extraction:
   • Use TEXTBEFORE and TEXTAFTER to extract parts of text strings based on different delimiters.
2. Splitting Text:
   • Apply the TEXTSPLIT function to split a text string into individual components and analyze the results.
3. Joining Text:
   • Use TEXTJOIN to combine multiple text strings into a single string, testing different delimiters and options.

Pro Tip: Excel 365’s latest text functions are powerful tools for text manipulation and analysis. Mastering these functions can significantly enhance your efficiency in handling text data. For more advanced techniques and personalized tutorials, Anjni Computer Education offers comprehensive resources to help you excel in Excel.