Webbo3 Data Analysis Bootcamp · Excel Module · Lesson 6

Excel Text Functions: Extracting, Cleaning, Changing Case, and Combining Text

A hands on lesson on the functions that fix, slice, and join text data, the exact problems each one solves, and the hidden traps that catch even experienced users.

Real data is rarely clean. Names arrive with extra spaces, product codes need splitting apart, addresses copied from a website carry invisible characters you cannot even see, and reports need first and last names joined back into one column. Text functions exist to solve exactly these problems without you retyping a single thing by hand. This lesson covers the four families of text functions you will reach for constantly: pulling text apart, cleaning it up, changing its case, and putting it back together.

1. LEFT, RIGHT, MID: Extracting Parts of Text

LEFT syntax: =LEFT(text, [num_chars])

RIGHT syntax: =RIGHT(text, [num_chars])

MID syntax: =MID(text, start_num, num_chars)

LEFT pulls a fixed number of characters starting from the very beginning of a text string, and RIGHT pulls a fixed number of characters starting from the very end. If cell A2 holds the phone number "0803-123-4567" and you want just the area code, =LEFT(A2,4) returns "0803". If you want the last four digits instead, =RIGHT(A2,4) returns "4567". MID is the one that confuses beginners most, because it needs three pieces of information instead of two: where to start counting from, and how many characters to grab once you start. If a product code reads "PROD-XYZ-1234" and you want only "XYZ," you count that X sits at character position 6, so =MID(A2,6,3) extracts exactly those three characters.

A practical workflow: splitting one column into three. Suppose a column holds full phone numbers in a fixed format like "0803-123-4567," always ten digits plus two hyphens in exactly the same positions every time. You can build three separate columns from one source column: =LEFT(A2,4) for the area code, =MID(A2,6,3) for the middle three digits, and =RIGHT(A2,4) for the last four. This only works cleanly because every entry follows the exact same fixed length and structure. If the lengths vary from row to row, for example names with different numbers of words, you would need to combine MID with FIND to locate a delimiter like a space or a hyphen dynamically rather than hardcoding a fixed position.

A trap worth knowing now. LEFT, RIGHT, and MID always return their result formatted as text, even when the characters extracted are digits that look exactly like a number. If you extract "1234" with RIGHT and then try to use it inside a SUM or a comparison formula expecting a real number, it may not behave the way you expect, since Excel is technically holding onto a text string, not a numeric value. If you genuinely need the extracted result to behave as a number, wrap the whole formula inside the VALUE function, for example =VALUE(RIGHT(A2,4)).

2. LEN, TRIM, CLEAN: Measuring and Cleaning Text

LEN syntax: =LEN(text)

LEN counts exactly how many characters are inside a text string, including every single space. It is simple on its own, but it becomes genuinely powerful as a diagnostic tool, because comparing the LEN of a cell before and after a cleaning operation tells you precisely whether that cell contained hidden characters you could not see on screen. If =LEN(A2) returns 14 but the text visually looks like it should only be 13 characters long, something invisible is hiding inside that cell.

TRIM syntax: =TRIM(text)

TRIM removes every leading space before the text, every trailing space after the text, and collapses any run of multiple spaces between words down to a single space. This single function fixes one of the most common, invisible causes of broken lookups in real spreadsheets. A VLOOKUP searching for "London" will silently fail to match a cell containing " London" with a leading space, even though both look identical to your eyes on screen. Running =TRIM(A2) on imported or copy pasted data before relying on it for any lookup or comparison is good practice by default.

CLEAN syntax: =CLEAN(text)

The genuinely important distinction between TRIM and CLEAN. These two functions are not interchangeable, even though they both get called "cleaning" functions. TRIM only removes the standard space character. CLEAN removes a completely different category of problem: the first 32 non printable ASCII control characters, things like line breaks, carriage returns, and tab characters that sometimes ride along invisibly in data copied from PDFs, websites, or exported database files. Neither function alone catches everything. A particularly stubborn case is the non breaking space, common in text copied from web pages, which has character code 160. This looks exactly like a normal space but sits outside the range that both TRIM and CLEAN are built to catch, so it survives both functions untouched. To remove it specifically, you need =SUBSTITUTE(A2,CHAR(160)," ") first, and only then can TRIM clean up the regular spaces that remain. A safe, standard cleanup pattern for genuinely messy imported text is =TRIM(CLEAN(A2)), running CLEAN first to strip control characters, then TRIM to tidy up spacing.

3. UPPER, LOWER, PROPER: Changing Case

Syntax: =UPPER(text), =LOWER(text), =PROPER(text)

UPPER converts every letter in a text string to capital letters, LOWER converts every letter to lowercase, and PROPER capitalises the first letter of each separate word while lowercasing the rest, which is exactly the format most names and titles should be stored in. If column A holds customer names typed inconsistently, some in all caps, some in all lowercase, some half and half, =PROPER(A2) standardises every version into a clean "Chidinma Okafor" style format in one pass.

A genuine limitation worth knowing before you rely on PROPER blindly. PROPER capitalises the first letter after any space or certain punctuation, with no understanding of context or meaning. This means it will turn "st" into "St" and "rd" into "Rd" inside street names, which is usually exactly what you want for addresses. But it will also capitalise an abbreviation that should stay lowercase, for example turning "apt" for apartment into "Apt," which might break a downstream lookup or database match that specifically expects the lowercase version. Always glance through a sample of PROPER's output on your actual data before applying it across an entire column, rather than assuming it behaves identically on every dataset.

4. CONCATENATE, CONCAT, TEXTJOIN: Combining Text

CONCATENATE syntax: =CONCATENATE(text1, [text2], ...)

CONCAT syntax: =CONCAT(text1, [text2], ...)

TEXTJOIN syntax: =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

All three functions join pieces of text together into one combined string, but they solve the problem at three different levels of capability, and understanding why the newer two exist explains exactly when to reach for each one.

CONCATENATE is the original, older function. It joins whatever you give it directly, with no automatic spacing and no automatic punctuation between pieces. If A2 holds "Chidinma" and B2 holds "Okafor," =CONCATENATE(A2," ",B2) returns "Chidinma Okafor," but notice you had to manually type the space yourself as a separate piece in quotation marks. CONCATENATE's real limitation is that it cannot accept a whole range like A2:A10 in one go. You must list every single cell individually, which becomes genuinely tedious once you are joining more than three or four cells.

CONCAT is the modern replacement. It behaves almost identically to CONCATENATE, with one key improvement: it can accept an entire range as a single argument. =CONCAT(A2:A10) joins ten cells together in one short formula, something CONCATENATE simply cannot do without listing each cell out one by one. The trade off is that CONCAT still has no built in way to insert a delimiter automatically between each joined piece, so without manually adding spaces or commas yourself, everything gets squeezed together with no separation at all.

TEXTJOIN is the most capable of the three. It solves both of CONCAT's limitations at once. You specify your delimiter exactly once, as the very first argument, rather than retyping it between every single piece, and you also get a second argument, ignore_empty, which when set to TRUE automatically skips any blank cells in your range rather than leaving an awkward double comma or double space where that blank cell would have been. =TEXTJOIN(", ", TRUE, A2:E2) joins every non blank cell from A2 through E2 with a comma and space between each one, cleanly skipping any cell in that row that happens to be empty.

A real use case for this bootcamp. Imagine a client intake form where columns F through J list different investment types a client is interested in, and some clients only ticked two or three of those columns while leaving the rest blank. =TEXTJOIN(", ", TRUE, F2:J2) produces a clean, comma separated summary of only the investment types that client actually selected, with no stray commas or blank gaps marking where the empty cells used to be.

A genuine trap to watch for with TEXTJOIN. A cell that looks blank but actually contains a single space character is not treated as empty by TEXTJOIN's ignore_empty setting, since technically that cell does contain text, just text that happens to be invisible. If your delimiter list still shows unexpected empty looking gaps even with ignore_empty set to TRUE, wrap each reference in TRIM first, for example =TEXTJOIN(", ", TRUE, TRIM(F2:J2)), which clears out any stray spaces sitting in supposedly blank cells before the joining happens. Also worth knowing for compatibility purposes: TEXTJOIN and CONCAT only work in Excel 2019 and later, including Microsoft 365. If you ever share a workbook with someone using an older version, formulas built with either function will show a #NAME? error on their machine, and CONCATENATE or the plain ampersand operator are the safer, fully backward compatible choices in that situation.

Using AI to Move Faster in Excel

Text cleaning and manipulation is exactly where the gap between Flash Fill, manual text functions, and AI inside Excel becomes most visible, because the right tool genuinely depends on how messy and inconsistent the underlying data actually is.

1. Let Copilot pick the right combination of functions for you.
Splitting a name, extracting a code, or standardising case can often be solved with several different formula combinations, and beginners frequently are not sure which one fits their exact data. Select your range and ask Copilot something like "Extract the area code from these phone numbers" or "Combine these first and last name columns into one, separated by a space, skipping any blank middle names." Copilot typically returns a working formula using the correct function, often TEXTJOIN where a beginner might have reached for the more limited CONCATENATE out of habit, and explains why it chose that approach.

2. Ask Copilot to find hidden characters you cannot see.
Since non breaking spaces and control characters are invisible on screen by definition, manually spotting them is genuinely difficult without already knowing the LEN comparison trick from this lesson. A useful prompt is "Check this column for hidden or non breaking spaces that might be breaking lookups" before you spend time troubleshooting a failed VLOOKUP that looks, to your eyes, like it should obviously be matching.

3. Use natural language for case standardisation, then verify PROPER's exceptions yourself.
If your dataset contains abbreviations, suffixes, or specific formatting that PROPER would incorrectly capitalise, describe the actual rule to Copilot, for example "Capitalise each name properly but keep 'apt' and 'rd' lowercase in this address column." AI can often build a more tailored formula for this specific exception than the plain PROPER function alone, but you still need to read through a sample of the output, since the exception list you describe might not be exhaustive on the first try.

4. Verify the actual character count, not just the visual appearance.
Text cleaning is uniquely deceptive because a cell can look perfectly fine on screen while still containing invisible characters that break formulas elsewhere. Whether you cleaned a column manually with TRIM and CLEAN, or asked Copilot to do it, always run a quick LEN check before and after on a handful of cells to confirm something measurable actually changed, rather than trusting that the cell "looks right" now.

A habit worth building from this lesson onward: whenever a lookup or comparison formula fails on data that looks identical to your eyes, suspect a hidden character or stray space before you suspect your formula logic. The LEN before and after comparison from this lesson, or a direct question to Copilot about hidden characters, will usually find the real cause faster than rereading your own formula syntax five times over.

Next lesson: logical formulas, starting with IF, AND, and OR.