Text Functions: LEFT, RIGHT, MID, CONCAT, TEXT
Real-world data rarely arrives clean — names come combined in one cell, product codes need splitting, and numbers need to be dressed up as readable text. This lesson covers LEFT, RIGHT, and MID for slicing strings, CONCAT and the & operator for joining them, TEXT for formatting numbers as strings, and TRIM/CLEAN for scrubbing imported junk. These are the tools that turn a messy CSV export into a usable spreadsheet.
Why this skill matters professionally
Almost every data import — from a payroll system, a CRM export, a scanned form, or a copy-pasted web table — needs some text cleanup before it's usable, and knowing these functions means you don't need IT or a developer to fix it for you. Admin assistants, HR coordinators, and bookkeepers regularly need to split full names into first/last, extract area codes from phone numbers, pull the year out of an ID number, or build a readable summary sentence that blends text and numbers — all of which rely on exactly the functions in this lesson.
Learning objectives
- Extract parts of strings and combine them into new values.
- Format numbers as text with the TEXT function.
Background concepts
Text functions operate character by character
LEFT, RIGHT, and MID count characters from a fixed starting point, not words. "Smith, John" and "Smith,John" behave differently in a formula because of that one extra space — always check for consistent spacing before slicing.
Joining text: CONCAT vs. the & operator
CONCAT(A2," ",B2) and A2&" "&B2 do the same thing — join text together. & is faster to type for two or three pieces; CONCAT is easier to read when joining many values or a whole range.
TEXT turns numbers into formatted strings
A raw number like 1500 is stored as a number, but TEXT(1500,"$#,##0.00") converts it to the string "$1,500.00" so it can be embedded inside a sentence built with &, since Excel can't automatically format a number when it's glued into text.
Imported data hides invisible problems
Text pasted from the web or another system often carries leading/trailing spaces or non-printable characters that look invisible but break exact-match formulas like VLOOKUP or IF comparisons. TRIM and CLEAN exist specifically to strip these out.
Step-by-step
1. 1. Extract characters from the left or right
=LEFT(A2,3) returns the first 3 characters of A2; =RIGHT(A2,4) returns the last 4. Useful for pulling a 3-letter product prefix or a 4-digit year from the end of an ID.
2. 2. Extract characters from the middle
=MID(A2,5,3) starts at character 5 and grabs the next 3 characters. If A2 is "INV-2024-105", MID(A2,5,4) pulls "2024".
3. 3. Find a dynamic split point
Instead of hardcoding a position, use FIND to locate a delimiter: =LEFT(A2,FIND(",",A2)-1) grabs everything before a comma, regardless of how long the name is.
4. 4. Join text with the & operator
=A2&" "&B2 combines a first and last name with a space between them. Add more pieces the same way: =A2&", "&B2&" - "&C2.
5. 5. Join text with CONCAT
=CONCAT(A2," ",B2) does the same job and reads more cleanly with several arguments; CONCAT can also take a whole range like CONCAT(A2:A5).
6. 6. Format numbers with TEXT
=TEXT(A2,"$#,##0.00") turns 1500 into "$1,500.00". Combine it into a sentence: ="Total due: "&TEXT(A2,"$#,##0.00").
7. 7. Format dates with TEXT
=TEXT(TODAY(),"mmmm d, yyyy") turns today's date into something like "March 4, 2025" for use in printed letters or labels.
8. 8. Clean imported data
=TRIM(A2) removes extra spaces between and around words (but keeps single spaces); =CLEAN(A2) strips non-printable control characters often left behind by exports from other systems. Combine as =TRIM(CLEAN(A2)) for maximum cleanup.
9. 9. Convert formulas to static values
After slicing and combining text, copy the results and use Paste Special → Values to lock them in as plain text before deleting the original messy column.
Real-world workplace examples
Splitting a full name column
=LEFT(A2,FIND(" ",A2)-1) and =MID(A2,FIND(" ",A2)+1,50) split "John Smith" into separate First and Last columns for a mail merge.
Building an email address
=LOWER(B2)&"."&LOWER(C2)&"@company.com" auto-generates a standardized email from first and last name.
Extracting a product category code
=LEFT(SKU,3) pulls the 3-letter category prefix from a SKU like "ELE-40021" to group products by department.
Formatting a dynamic report title
="Sales Report — "&TEXT(TODAY(),"mmmm yyyy") automatically labels a monthly report with the current month and year.
Cleaning a CRM export
A sales ops coordinator runs =TRIM(CLEAN(A2)) across an entire exported contact list to fix invisible characters that were causing VLOOKUPs against another list to fail.
Practical scenarios
The invisible space bug
A payroll coordinator's VLOOKUP keeps returning #N/A even though the employee ID 'visually' matches between two sheets. After investigating, she discovers one system exports IDs with a trailing space ("EMP1023 ") while the other doesn't. Wrapping both lookup values in TRIM() — =VLOOKUP(TRIM(A2),...) — fixes every mismatch instantly, without having to manually inspect a hundred rows.
Building a merge-ready mailing list
A nonprofit's donor list has full names in one column but the mail-merge software needs separate First Name and Last Name fields. Using FIND to locate the space, combined with LEFT and MID, the coordinator splits 800 names in seconds instead of manually retyping them, and catches the few two-word first names (like 'Mary Ann Smith') that need a manual fix by sorting for unusually long results.
Common mistakes beginners make
Hardcoding a fixed character count for variable-length text
=LEFT(A2,5) assumes every entry is exactly 5+ characters before the part you want, but names and codes vary in length. Fix: use FIND to locate a delimiter dynamically instead of guessing a fixed number.
Forgetting TEXT when combining numbers into a sentence
="Total: "&A2 where A2 is 1500 returns "Total: 1500" with no currency formatting. Fix: wrap the number in TEXT with the desired format code.
Confusing CONCAT with CONCATENATE
CONCATENATE still works but is considered legacy; it also can't take a whole range as one argument the way CONCAT and TEXTJOIN can. Fix: use CONCAT or TEXTJOIN in modern workbooks.
Not cleaning data before lookups
Invisible trailing spaces or line breaks make exact-match formulas fail silently with #N/A. Fix: apply TRIM/CLEAN as a habit on any freshly imported text column.
Leaving formulas live after splitting names
If the original combined-name column is deleted while formulas still reference it, split columns turn into #REF! errors. Fix: Paste Special → Values on the results before removing the source column.
Best practices
- Always inspect a sample of imported text for hidden spaces or line breaks before building lookups against it.
- Prefer FIND-based dynamic splitting over hardcoded character counts whenever text length varies.
- Wrap any number embedded in a text sentence with TEXT() and the correct format code.
- Use TEXTJOIN with a delimiter when combining more than a few cells, especially if some might be blank.
- Convert split/cleaned results to static values with Paste Special before deleting source columns.
- Standardize case with UPPER/LOWER/PROPER before comparing or merging text from different sources.
- Test text formulas against edge cases: extra-long names, missing middle initials, double spaces.
Professional tips
- TEXTJOIN(", ",TRUE,A2:A10) joins a whole range with a delimiter and automatically skips blanks — better than chaining & for long lists.
- PROPER() capitalizes the first letter of each word — handy for cleaning up ALL CAPS or all lowercase name imports.
- Use Flash Fill (Ctrl+E) for quick one-off splits when you don't need a permanent formula — Excel guesses the pattern from your first example.
- FIND is case-sensitive; SEARCH is not — pick whichever matches your data's consistency.
- Combine LEN with other text functions, like MID(A2,4,LEN(A2)-3), when you need 'everything after position 3' without knowing the total length in advance.
Practice exercises
Split full names
Create a column of 10 full names and use FIND, LEFT, and MID to split them into First Name and Last Name columns.
Auto-build email addresses
Using first and last name columns, build a formula that generates a lowercase, period-separated company email address.
Format a dynamic summary sentence
Combine TEXT and & to build a sentence like 'Invoice #1042 for $2,350.00 is due March 15, 2025' pulling from three separate cells.
Clean a messy import
Paste in text with irregular spacing and apply TRIM and CLEAN to standardize it, then verify a VLOOKUP against a matching list now works.
Review questions
What's the difference between LEFT, RIGHT, and MID?
LEFT takes a set number of characters from the start, RIGHT takes them from the end, and MID takes a set number starting at any position you specify.
Why would you use FIND instead of a hardcoded number in LEFT?
FIND locates a delimiter dynamically so the formula works correctly even when text length varies row to row.
Why does TEXT matter when joining a number into a sentence with &?
Without TEXT, the number displays in its raw, unformatted form (no currency symbol, commas, or date formatting) inside the combined string.
What's the difference between TRIM and CLEAN?
TRIM removes extra spaces (leading, trailing, and repeated internal spaces down to one), while CLEAN removes non-printable control characters that don't show visually but can break formulas.
Why would a VLOOKUP fail even though the values look identical?
A hidden trailing space or non-printable character in one of the values makes them not an exact match even though they look the same visually.
Key takeaways
- LEFT, RIGHT, and MID slice text by character position, not by word.
- Use FIND to locate delimiters dynamically instead of hardcoding character counts.
- & and CONCAT both join text; TEXTJOIN is best for many cells or ranges with a delimiter.
- TEXT() must be used to format numbers or dates before embedding them in a joined string.
- TRIM removes extra spaces; CLEAN removes non-printable characters — imported data often needs both.
- Paste Special → Values locks in results before you delete the source column.
- PROPER/UPPER/LOWER standardize case for cleaner comparisons and merges.
Frequently asked questions
Is MID's second argument the position or the count?
The second argument (start_num) is the starting position; the third argument (num_chars) is how many characters to take from there.
Can these functions work on numbers, not just text?
They treat numbers as text automatically, but the result is always text, so you may need VALUE() to convert it back to a number for math.
What's Flash Fill and how is it different from a formula?
Flash Fill (Ctrl+E) guesses a text pattern from an example and fills it down as static values instantly — fast, but not dynamic if source data changes.
Does TEXTJOIN skip blank cells automatically?
Yes, if you set its second argument (ignore_empty) to TRUE.
Why does CONCAT sometimes show as CONCATENATE in older files?
CONCATENATE is the legacy function kept for backward compatibility; CONCAT and TEXTJOIN are the modern replacements.
How do I remove line breaks inside a cell?
CLEAN removes most non-printable characters including line breaks (Alt+Enter breaks); for stubborn ones, SUBSTITUTE(A2,CHAR(10)," ") targets line breaks specifically.
Can I combine TEXT with IF to build conditional messages?
Yes — for example =IF(A2>0,"Balance: "&TEXT(A2,"$#,##0.00"),"Paid in full") builds a different message depending on the value.
Related lessons
12 min • Beginner
15 min • Easy
20 min • Intermediate
20 min • Advanced