Mastering Excel's Text Functions: LEFT, RIGHT, MID, FIND, SUBSTITUTE, and TEXTJOIN for Expert-Level Data Cleaning and Transformation | Wicked Smart Data
Mastering Excel's Text Functions: LEFT, RIGHT, MID, FIND, SUBSTITUTE, and TEXTJOIN for Expert-Level Data Cleaning and Transformation
Real data is messy — inconsistent formats, mismatched delimiters, mixed cases, and hidden characters. This deep-dive lesson teaches you to wield Excel's six core text functions as a unified toolkit, building formulas that parse, clean, and reconstruct strings with precision. You'll leave with production-ready patterns for the text manipulation problems that show up every day in serious data work.
Mastering Excel's Text Functions: LEFT, RIGHT, MID, FIND, SUBSTITUTE, and TEXTJOIN for Expert-Level Data Cleaning and Transformation
Introduction
You've just received a data export from your company's CRM. Column A contains full names like "Smith, John A." — last name first, comma-separated, with middle initials. Column B has phone numbers formatted as "(312) 555-0192" but your database wants "3125550192". Column C contains product SKUs like "WH-BLU-LRG-2024" where you need to extract just the color code. You've got 12,000 rows.
This is the real world of data work. Systems don't agree on formats. Legacy data is messy. People enter information inconsistently. And the gap between "raw data" and "analysis-ready data" is almost always filled with text manipulation. The six functions you'll master in this lesson — LEFT, RIGHT, MID, FIND, SUBSTITUTE, and TEXTJOIN — are the core toolkit for bridging that gap without ever opening a Python script or writing a single line of SQL.
By the end of this lesson, you'll be able to build sophisticated text-parsing formulas that handle real-world messiness: variable-length strings, inconsistent delimiters, nested extractions, and multi-step transformations. You'll understand not just what each function does but why it works the way it does, so you can combine them intelligently under pressure.
What you'll learn:
How LEFT, RIGHT, and MID work under the hood and where naive usage breaks down
How FIND (and its case-insensitive twin SEARCH) enables dynamic, position-aware extraction
How to build nested formulas that locate delimiters and extract text between them
How SUBSTITUTE handles multi-step cleaning, character replacement, and a clever trick for counting occurrences
How TEXTJOIN replaces the painful CONCATENATE pattern and handles arrays elegantly
How to combine all six functions into production-grade data transformation pipelines
Prerequisites
You should be comfortable with:
Writing basic Excel formulas with cell references and function arguments
The concept of nested functions (functions as arguments to other functions)
Understanding How Excel Sees Text
Before we touch a single function, let's establish something foundational: Excel treats every text string as a sequence of characters with positions numbered from left to right, starting at 1.
The string "Smith, John" looks like this to Excel:
Position: 1 2 3 4 5 6 7 8 9 10 11
Character: S m i t h , J o h n
Every text function we'll cover operates on these positions. LEFT starts from position 1. RIGHT counts back from the end. MID starts at any position you specify. FIND returns the position number of a character you're searching for. Once you internalize this positional model, nested formulas that look intimidating become logical.
One more important nuance: spaces are characters. Position 7 in "Smith, John" is a space. Forgetting this causes more off-by-one errors than anything else in text work. When you're extracting text around a delimiter like ", " (comma-space), you're dealing with a two-character delimiter, not one.
Key insight
Excel text functions are case-sensitive in their behavior regarding FIND, but case-insensitive variants exist. More importantly, they're byte-position functions — they operate on character positions, not semantic units. A space is as real a character as a letter.
text is the string (or cell reference), and num_chars is how many characters to return. If you omit num_chars, it defaults to 1.
Simple case: extracting a two-letter state code from "Chicago, IL":
=RIGHT("Chicago, IL", 2)
Returns: IL
Extracting a country code from phone prefix "+1-312-555-0192":
=LEFT("+1-312-555-0192", 2)
Returns: +1
So far, so good. Here's where it starts to matter: what happens when your data isn't uniformly formatted?
Consider a column of city-state combinations:
"Chicago, IL"
"New York, NY"
"Los Angeles, CA"
RIGHT(A1, 2) works perfectly because state codes are always exactly 2 characters. But what about extracting the city name? "Chicago" is 7 characters. "New York" is 8. "Los Angeles" is 11. You cannot hardcode a length — you need to calculate it dynamically. That's where FIND enters the picture, and we'll combine them shortly.
Warning
Never hardcode character counts in LEFT or RIGHT unless you have an absolute guarantee your data format is fixed. Even then, document why the number is hardcoded. Data schemas change, and a formula like =LEFT(A1, 6) with no explanation becomes a maintenance nightmare six months later.
RIGHT for Suffix Extraction
RIGHT shines when you need file extensions, domain names, or unit codes that always trail the string at known lengths:
// Extract file extension (always 4 chars: .csv, .txt, .pdf)
=RIGHT(A1, 4)
// Extract year from a product code like "PROD-2024-Q3"
=RIGHT(A1, 7) // Returns "2024-Q3"
But the more professional pattern — even for "fixed" formats — is to calculate the length dynamically using LEN minus a FIND result. We'll build that pattern in the FIND section.
MID: Extraction from Anywhere
MID is the most flexible extraction function:
=MID(text, start_num, num_chars)
start_num is the position to start extracting from. num_chars is how many characters to pull.
From a product SKU like "WH-BLU-LRG-2024", extract "BLU":
=MID("WH-BLU-LRG-2024", 4, 3)
Position 4 is "B", and we take 3 characters: "BLU". This works if the SKU format is rigidly fixed. For variable-length segments, we again need FIND.
MID's Behavior at the Edges
Two edge cases worth knowing:
Over-extraction: If num_chars is larger than the remaining characters, MID returns everything it can without erroring. =MID("Hello", 4, 100) returns "lo" — not an error. This is useful when you want "everything from position X to the end" and don't know the exact length.
Invalid start position: If start_num is less than 1, MID returns a #VALUE! error. If start_num is greater than the string length, it returns an empty string (not an error). These asymmetric behaviors trip up a lot of people.
=MID("Hello", 0, 3) // Returns #VALUE! — start_num must be >= 1
=MID("Hello", 10, 3) // Returns "" — past the end, no error
Tip
The "over-extraction" behavior of MID is intentional and useful. When you're using FIND to locate a delimiter and want everything after it, use =MID(A1, FIND("-", A1)+1, LEN(A1)) — the LEN(A1) as the num_chars argument guarantees you capture everything remaining, even if the actual count is smaller.
FIND and SEARCH: Locating Delimiters Dynamically
FIND is the function that makes LEFT, RIGHT, and MID genuinely powerful:
=FIND(find_text, within_text, [start_num])
It returns the position number of the first occurrence of find_text inside within_text. The optional start_num lets you begin searching from a specific position — critical for finding the second, third, or Nth occurrence of a delimiter.
=FIND(",", "Smith, John") // Returns 6 — the comma is at position 6
=FIND(" ", "Smith, John") // Returns 7 — the space is at position 7
SEARCH is FIND's case-insensitive sibling and also supports wildcards (? for any single character, * for any sequence). FIND is case-sensitive and does not support wildcards. For most data cleaning work, FIND is preferable because it's more precise.
The City Extraction Problem, Solved
Back to "Chicago, IL" — extracting the city name. The delimiter is ", " (comma-space) at a variable position. Here's the formula:
=LEFT(A1, FIND(",", A1) - 1)
Let's trace through "New York, NY":
FIND(",", "New York, NY") returns 9 — the comma is at position 9
9 - 1 = 8
LEFT("New York, NY", 8) returns "New York"
The -1 is the key move: FIND gives you the position of the comma itself, so you subtract 1 to stop just before it.
For "Los Angeles, CA":
FIND returns 12
12 - 1 = 11
LEFT returns "Los Angeles"
This works regardless of city name length.
Extracting the State: RIGHT with Dynamic Length
Now extract the state code from the same string. The state is always the last 2 characters, so RIGHT(A1, 2) works here — but let's build the dynamic version as a teaching exercise:
=RIGHT(A1, LEN(A1) - FIND(",", A1) - 1)
For "New York, NY":
LEN("New York, NY") = 12
FIND(",", ...) = 9
12 - 9 - 1 = 2
RIGHT("New York, NY", 2) = "NY"
The logic: total length minus position of comma minus 1 (for the space after the comma) gives us the number of characters after the ", " delimiter.
Finding the Nth Occurrence of a Delimiter
Here's where the start_num argument becomes essential. Consider extracting "BLU" from "WH-BLU-LRG-2024" without hardcoding the position:
// Position of first dash
=FIND("-", "WH-BLU-LRG-2024") // Returns 3
// Position of second dash — start searching AFTER the first one
=FIND("-", "WH-BLU-LRG-2024", FIND("-", "WH-BLU-LRG-2024") + 1) // Returns 7
Now extract the segment between the first and second dash (the color code):
This looks intimidating, but let's decompose it for "WH-BLU-LRG-2024":
FIND("-", A1) = 3 → start position is 3+1 = 4 (the "B")
Second FIND = 7 → num_chars = 7 - 3 - 1 = 3
MID("WH-BLU-LRG-2024", 4, 3) = "BLU"
Tip
When you find yourself using FIND three or more times in a single formula, consider breaking it into helper columns. Name those columns clearly (e.g., "FirstDashPos", "SecondDashPos") and assemble the final result in a separate column. This approach is dramatically easier to audit and debug. See Mastering Excel Formula Auditing: Trace Precedents, Dependents, and Evaluate Formulas to Build Error-Free Workbooks for the tools that make this process efficient.
Handling FIND Errors Gracefully
FIND returns #VALUE! when the search string isn't found. In real data, this happens constantly — not every row has the pattern you expect. Wrap FIND in IFERROR to handle missing delimiters:
=IFERROR(LEFT(A1, FIND(",", A1) - 1), A1)
This says: "Try to extract everything before the comma. If there's no comma, just return the whole string." That's often the right fallback when a delimiter is optional.
SUBSTITUTE: Cleaning, Replacing, and Counting
SUBSTITUTE replaces occurrences of one text string with another:
The optional instance_num specifies which occurrence to replace. Without it, all occurrences are replaced.
Phone Number Cleaning
The phone number problem from the introduction: "(312) 555-0192" → "3125550192". You need to remove three different characters: "(", ")", " ", and "-". Chain SUBSTITUTE calls:
This pattern of chained SUBSTITUTE calls is one of the most common patterns in professional data cleaning. It's not elegant, but it's explicit, auditable, and reliable.
Note
Excel doesn't have a native "remove all non-numeric characters" function. For that kind of aggressive cleaning, chained SUBSTITUTE handles known offenders, while more complex cases might require a LAMBDA function (Excel 365+) or VBA. If you're building automation for this kind of cleaning at scale, Introduction to VBA: Write Your First Excel Macro and Automate Repetitive Tasks covers the automation path.
Targeted Replacement with instance_num
The instance_num argument is underused and powerful. Consider a serial number like "AB-123-456-789" where you want to replace only the second hyphen:
=SUBSTITUTE("AB-123-456-789", "-", ".", 2)
Returns: "AB-123.456-789"
This is useful when you're reformatting structured codes where delimiter position is semantically meaningful.
The SUBSTITUTE Trick for Counting Characters
Here's one of the cleverest applications: using SUBSTITUTE to count how many times a character appears in a string. The logic is:
Count the total length of the string
Remove all instances of the target character
Count the new length
The difference is the count
=LEN(A1) - LEN(SUBSTITUTE(A1, "-", ""))
For "WH-BLU-LRG-2024":
LEN = 16
SUBSTITUTE removes hyphens: "WHBLURLG2024" — LEN = 12 (wait, let me recount: W-H-B-L-U-L-R-G-2-0-2-4 = 12 characters + the 3 hyphens we removed = 15 original... actually "WH-BLU-LRG-2024" is W,H,-,B,L,U,-,L,R,G,-,2,0,2,4 = 15 chars, 3 hyphens, so 15-12=3)
16 - 13 = 3 — three hyphens
This technique becomes valuable when combined with MID to find the Nth occurrence of a delimiter — we'll see that in the advanced patterns section.
Normalizing Inconsistent Data
SUBSTITUTE excels at normalization. Consider a column where data entry inconsistencies produced "N/A", "n/a", "NA", "N A", and "#N/A" all meaning the same thing:
Be careful with SUBSTITUTE when the replacement string could itself contain the search string. For instance, substituting "0" with "00" and then substituting "00" with something else will produce unexpected results because the newly created "00" will also be matched. Plan your substitution order deliberately, and always test with edge cases.
Case-Sensitive SUBSTITUTE
Unlike SEARCH, SUBSTITUTE is always case-sensitive. =SUBSTITUTE("Hello World", "hello", "Hi") returns "Hello World" unchanged because "hello" ≠ "Hello". To do case-insensitive replacement, you'll need to normalize case first or use a FIND-based workaround:
This pattern — find the position with SEARCH, then rebuild the string with LEFT, a literal replacement, and MID — is the manual approach for case-insensitive replacement.
TEXTJOIN: The Professional's Concatenation Tool
Before TEXTJOIN (introduced in Excel 2019 and Office 365), joining multiple values required either a chain of & operators or CONCATENATE — both of which required you to manually specify every cell. TEXTJOIN changes the game:
delimiter is the separator string. ignore_empty is TRUE or FALSE — when TRUE, empty cells are silently skipped rather than producing double-delimiters.
Basic Usage
Rebuild a full name from separate first, middle, and last name columns:
=TEXTJOIN(" ", TRUE, A2, B2, C2)
If B2 (middle name) is empty, the result is "John Smith" not "John Smith" (double space). The ignore_empty = TRUE argument handles the optional middle name cleanly.
This is an array formula — in Excel 365 it works without Ctrl+Shift+Enter. In older versions, you need to enter it with Ctrl+Shift+Enter. The IF generates an array of email addresses where department matches, or empty strings otherwise. TEXTJOIN then joins only the non-empty values.
Key insight
TEXTJOIN with an IF array is one of the cleanest ways to build conditional string aggregations in Excel — tasks that would require GROUP BY and STRING_AGG in SQL. Combined with the dynamic array functions covered in Dynamic Arrays: FILTER, SORT, and UNIQUE Explained, this pattern becomes even more powerful.
Reconstructing After Parsing
A common workflow: parse data apart, transform the pieces, then rejoin. Consider a name in "Last, First Middle" format that you need to convert to "First Middle Last":
// A1: "Smith, John Alan"
// Last name: everything before the comma
=LEFT(A1, FIND(",", A1) - 1) // "Smith"
// First + middle: everything after the ", "
=MID(A1, FIND(",", A1) + 2, LEN(A1)) // "John Alan"
Wrapping the whole formula in one line is possible here because each component is clean and readable. For more complex transformations, break into helper columns.
Advanced Patterns: Combining All Six Functions
This is where professional-level text manipulation lives — combining functions into pipelines that handle realistic, messy data.
Pattern 1: Extracting Email Domain
From a list of email addresses, extract the domain (everything after "@"):
=MID(A1, FIND("@", A1) + 1, LEN(A1))
This uses the over-extraction behavior of MID — LEN(A1) is almost certainly larger than the remaining characters after "@", but MID just returns what's available.
Extract only the domain name without TLD (e.g., "gmail" from "user@gmail.com"):
FIND(".", A1, 6) = 11 (the dot after "gmail", searching from position 6)
MID(A1, 6, 11 - 5 - 1) = MID(A1, 6, 5) = "gmail"
Pattern 2: Extracting the Nth Segment from a Delimited String
This is a classic problem: from "WH-BLU-LRG-2024", extract the Nth segment. We want a formula that works for any N without hardcoding positions.
The approach uses SUBSTITUTE to convert the Nth hyphen into a unique marker, then MID and FIND to extract around it. We'll use a pipe character "|" as a temporary marker (replace with something that won't appear in your data):
// Extract Nth segment from a hyphen-delimited string
// N is in cell B1, string is in A1
=MID(
SUBSTITUTE(A1, "-", REPT("|", 100), B1-1),
FIND("|", SUBSTITUTE(A1, "-", REPT("|", 100), B1-1)),
FIND("-", SUBSTITUTE(A1, "-", REPT("|", 100), B1)) - FIND("|", SUBSTITUTE(A1, "-", REPT("|", 100), B1-1)) - 1
)
This is getting complex — let's use the cleaner approach that's easier to understand and extend.
A more practical solution uses a helper-column approach with TEXTJOIN and FILTERXML (Excel 2013+) or the SUBSTITUTE character-counting trick:
// Find position of the (N-1)th delimiter
// For segment 2 (N=2) in "WH-BLU-LRG-2024":
// Start position = position after the (N-1)th hyphen
=IFERROR(
FIND(CHAR(1), SUBSTITUTE(A1, "-", CHAR(1), B1-1)) + 1,
1
)
CHAR(1) is a non-printable character unlikely to appear in real data. We substitute the (N-1)th hyphen with it, then FIND that character, add 1 to get the start of our segment.
For the end position, substitute the Nth hyphen with CHAR(1) and find it again. The difference gives us num_chars.
Tip
For truly complex multi-segment extractions, consider using Power Query instead of formulas. Power Query's "Split Column by Delimiter" handles these cases with a few clicks and is far easier to audit and maintain. That said, understanding the formula approach is essential for one-off transformations and when you're working in environments without Power Query.
Pattern 3: Cleaning Product Descriptions — A Full Pipeline
Let's walk through a realistic end-to-end scenario. Your source data (column A) contains product descriptions like:
" Widget Pro (Model: WP-2024) -- See catalog for details "
You need:
Column B: Cleaned description (trimmed, extra spaces removed)
Column C: Model number extracted
Column D: Year extracted from model number
Column B — Clean the description:
=TRIM(SUBSTITUTE(A1, " ", " "))
TRIM removes leading/trailing spaces and collapses internal multiple spaces to one. The SUBSTITUTE handles any double-spaces TRIM might miss in certain contexts.
Actually, the professional approach chains it:
=TRIM(A1)
TRIM alone handles most spacing issues. Use SUBSTITUTE only if you have non-breaking spaces (common in web-scraped data):
=TRIM(SUBSTITUTE(A1, CHAR(160), " "))
CHAR(160) is the non-breaking space character that TRIM does not remove.
Column C — Extract the model number (everything between "Model: " and ")"):
FIND("Model: ", B1) finds the position of "Model: "
Add 7 (the length of "Model: ") to get to the start of the model number
FIND(")", B1, ...) finds the closing parenthesis, searching from the "Model: " position to avoid earlier parentheses if any
Subtract the start position and 7 to get the character count
Column D — Extract the year from the model number:
=RIGHT(C1, 4)
Here the hardcoded 4 is justified: years are always 4 digits, this is a terminal format, and we've already isolated the model number string in column C.
Wrap everything in IFERROR for rows that don't follow this pattern:
This is the kind of formula you'll actually write in production — defensive, explicit, and documented through helper columns.
Pattern 4: Rebuilding Standardized Output with TEXTJOIN
After extracting pieces, you often need to reconstruct a standardized format. Suppose you've parsed first names (C2), last names (D2), and department codes (E2) from a messy import, and need to build standardized user IDs like "doe.jane.mktg":
TEXTJOIN with an empty delimiter and ignore_empty=TRUE is a clean alternative to chaining & operators, especially when some components might be conditionally empty.
Performance Considerations
When you're running these formulas across tens of thousands of rows, performance matters.
FIND is expensive when nested: A formula that calls FIND three times evaluates three separate full-string scans. In a 50,000-row dataset, that's 150,000 FIND evaluations for a single formula column. On modern hardware this is usually acceptable, but be aware it compounds.
Volatile vs. non-volatile: LEFT, RIGHT, MID, FIND, SUBSTITUTE, and TEXTJOIN are all non-volatile — they only recalculate when their input cells change, not on every workbook recalculation. This makes them safe to use extensively.
Helper columns beat mega-formulas: A formula that nests FIND four times to avoid a helper column is slower than two helper columns and a final simple formula. The calculation engine evaluates each function call independently — it doesn't cache intermediate FIND results within a formula. Breaking a complex formula into helper columns isn't just a readability improvement; it's a performance optimization.
TEXTJOIN with large ranges:=TEXTJOIN(",", TRUE, A1:A100000) on a very large range can be slow. If you're joining more than a few thousand values, consider whether this is the right tool — that output is almost certainly going to be unwieldy anyway.
Key insight
The formula =MID(A1, FIND("-",A1)+1, FIND("-",A1,FIND("-",A1)+1)-FIND("-",A1)-1) calls FIND three times. Replace those three calls with three helper columns, and your worksheet calculates three times faster for that column, while also being dramatically easier to debug. Clean data work prioritizes maintainability over formula minimalism.
Hands-On Exercise
Work through this exercise with a real dataset to solidify your skills.
Setup
Create a new worksheet and enter the following data in column A, rows 2–8 (row 1 is the header "Raw Employee Data"):
A2: "Johnson, Marcus T. | Sales | EMP-2019-00042"
A3: "Patel, Ananya | Engineering | EMP-2021-00187"
A4: "O'Brien, Siobhan M. | Marketing | EMP-2020-00093"
A5: "Chen, Wei | Sales | EMP-2022-00251"
A6: "Ramirez, Luis A. | Engineering | EMP-2018-00017"
A7: "Okonkwo, Adaeze | HR | EMP-2023-00312"
A8: "Fischer, Kurt H. | Finance | EMP-2021-00144"
Tasks
Task 1 — Column B: Last Name
Extract the last name (everything before the first comma).
=LEFT(A2, FIND(",", A2) - 1)
Task 2 — Column C: First Name
Extract the first name. It begins after , and ends at the space before the middle initial OR at |. Hint: FIND the second space after the comma, or FIND the | delimiter.
Wait — the | delimiter gives us a clean anchor. Everything between , and | is the first name (possibly with middle initial). Let's extract that entire segment first, then handle the middle initial separately.
This is a good example of when helper columns are your friend. Create column X as the first+middle segment, then extract just the first name from that.
Task 3 — Column D: Department
Extract the department (between the first and second " | " delimiters).
Bonus Task — Column H: Email Address
Build an email in the format "first.last@company.com" (lowercase, spaces in names replaced with nothing for last names like "O'Brien" — replace apostrophes too).
The most common error: forgetting whether you want the character at the FIND position or the character after it.
When extracting up to a delimiter: LEFT(A1, FIND(",", A1) - 1) — subtract 1 to exclude the delimiter.
When extracting after a delimiter: MID(A1, FIND(",", A1) + 1, ...) — add 1 to skip the delimiter itself.
When the delimiter is multiple characters (like " | "): add the full length of the delimiter string.
Debug approach: Use FIND alone in a separate cell to confirm the position, then build your LEFT/MID/RIGHT formula around that confirmed position.
Mistake 2: FIND Returns #VALUE! When the Delimiter Doesn't Exist
Always wrap FIND in IFERROR when your data isn't guaranteed to contain the delimiter:
=IFERROR(LEFT(A1, FIND(",", A1) - 1), A1)
This is critical in production formulas. One row without the expected delimiter will break the entire column if you don't handle it.
Mistake 3: TRIM Doesn't Remove Non-Breaking Spaces
Data copied from web pages or exported from certain systems contains non-breaking spaces (CHAR(160)). TRIM only removes regular spaces (CHAR(32)). If TRIM isn't cleaning a cell that looks like it has leading/trailing spaces:
=SUBSTITUTE(A1, "usa", "USA") won't change "USA" because the cases don't match. Normalize with UPPER or LOWER first when case-insensitive replacement is needed.
Mistake 5: Treating Numbers as Text (or Vice Versa)
When you extract a number using MID, the result is text. =MID("Code-2024", 6, 4) returns "2024" as a text string. If you need to use it in arithmetic, wrap in VALUE:
=VALUE(MID("Code-2024", 6, 4)) // Returns the number 2024, not text
Excel sometimes auto-coerces text-numbers in arithmetic, but not always — especially in comparisons. Be explicit about types.
Mistake 6: TEXTJOIN Not Available
TEXTJOIN is available in Excel 2019, Excel 365, and newer. In Excel 2016 and earlier, you'll need CONCATENATE or & chains. If you're sharing workbooks with users on older versions, check compatibility. For text joining in older Excel versions, you can also import data into Power Query which provides native merge operations.
Isolate each component. Put each nested function in its own cell and check the intermediate result.
Verify positions with FIND directly. Confirm the delimiter position in a separate cell before building around it.
Check for invisible characters. Use LEN to count characters — if a "5-character" string shows LEN=6, there's a hidden character.
Use Evaluate Formula. The Formulas tab > Evaluate Formula steps through each function evaluation one at a time.
Check data types. Is the source value actually text? Numbers stored as text behave differently with text functions than actual numbers.
Summary & Next Steps
You now have a complete toolkit for professional text manipulation in Excel. Let's consolidate what you've built:
LEFT, RIGHT, MID extract characters by position — static when your format is fixed, dynamic when combined with FIND. MID's over-extraction behavior is a feature, not a bug: use it to "take everything from position X to the end."
FIND is the engine that makes everything dynamic. It returns character positions, enabling length calculations that adapt to variable-length data. The start_num argument unlocks Nth-occurrence finding. Always wrap in IFERROR for production use.
SUBSTITUTE cleans data by replacing text — chain it to remove multiple characters, use instance_num for surgical replacement, and exploit the LEN-SUBSTITUTE trick to count character occurrences.
TEXTJOIN replaces CONCATENATE with a delimiter-aware, range-capable, empty-cell-skipping professional tool. Its array support makes conditional string aggregation possible without helper columns.
Together, these six functions handle the vast majority of text transformation problems you'll encounter in data work. The key skill isn't memorizing syntax — it's developing the pattern recognition to decompose a transformation problem into a sequence of positional operations.
Where to Go From Here
With text manipulation mastered, your next power move is combining these skills with lookup and aggregation functions. When you use VLOOKUP or XLOOKUP after text cleaning — for example, looking up a product code you've just extracted from a messy description field — you'll want to ensure your extracted values match your lookup table's format exactly. See VLOOKUP vs XLOOKUP: The Definitive Comparison for how to integrate lookups with cleaned data.
