Solved: Using the Excel LET function to extract the third-to-last word from a text string with varying delimiters

📌 The Problem Explained

In data processing, you often encounter “dirty” strings where information is separated by inconsistent characters like commas, semicolons, dashes, or spaces. For example, a logistics string might look like SHIP-2023;ORD_99,Priority;North_Warehouse;Ground.

If you need to extract a specific piece of data—such as the third-to-last segment (the “Priority” level)—traditional formulas using FIND and MID become incredibly complex and hard to maintain. The challenge is twofold: handling multiple delimiters simultaneously and counting positions from the end of the string rather than the beginning.

💡 The Quick Solution

Copy and paste this formula into your Formula Bar (assuming your text is in cell A2):

=LET(
    text, A2,
    delimiters, {" ",",",";","-","_"},
    segments, TEXTSPLIT(text, delimiters, , TRUE),
    CHOOSECOLS(segments, -3)
)

🛠️ Step-by-Step Breakdown

The LET function allows us to define names for calculations, making the formula faster and much easier to read. Here is how the logic is structured:

Step	Element	Description
1	text, A2	Assigns the source cell (A2) to the variable “text”.
2	delimiters, {" ",",",";","-","_"}	Defines a constant array of all possible characters that separate your words.
3	segments, TEXTSPLIT(...)	Uses TEXTSPLIT to break the string into an array. The TRUE argument at the end ensures that empty cells (caused by double delimiters like “, “) are ignored.
4	CHOOSECOLS(segments, -3)	This is the “Productivity Hack.” Using a negative index (-3) tells Excel to count from the right side of the array instead of the left.
5	Output	The final part of the LET function returns the result of the CHOOSECOLS operation.

⚠️ Common Mistakes to Avoid

• String Length Issues: If your text string contains fewer than three words/segments, the formula will return a #VALUE! error. To prevent this, you can wrap the formula in an IFERROR function (e.g., =IFERROR(LET(...), "N/A")).
• Missing Delimiters: If your string uses a delimiter not included in the curly brackets {...}, Excel will treat those two segments as a single word. Always inspect your data for unusual characters like pipes (|) or slashes (/) and add them to the delimiter array as needed.
• Excel Version: The LET and TEXTSPLIT functions are only available in Excel for Microsoft 365 and Excel 2021 (or later). If you are using an older version, these functions will return a #NAME? error.