Text Functions

In a perfect world, every piece of text in your spreadsheet would arrive clean, consistent, and exactly where you need it. In reality, data comes from exports, forms, databases, and emails — with extra spaces, mixed capitalisation, first and last names jammed together, city and country codes crammed into one cell. Text functions are how you untangle all of that. They let you extract, clean, transform, and combine text without touching the original data.

Changing Case — UPPER, LOWER, PROPER

These three functions are the simplest text functions to start with. They change the capitalisation of whatever text you give them — the original cell is not changed, the function just returns the converted version.

=UPPER(text)     Converts all letters to UPPERCASE
=LOWER(text)     Converts all letters to lowercase
=PROPER(text)    Capitalises The First Letter Of Each Word

Removing Spaces — TRIM and CLEAN

Extra spaces are one of the most common data quality issues. They are invisible but they break lookups, sorts, and comparisons. A cell that looks like it says "North" might actually say " North" with a leading space — and VLOOKUP will not find a match for it.

TRIM removes all leading and trailing spaces, and collapses multiple spaces between words down to a single space. It fixes most spacing problems in one go.

CLEAN removes non-printable characters — things like line breaks, tab characters, and control codes that sometimes sneak in when data is exported from other systems.

=TRIM(A2)           Removes extra spaces
=CLEAN(A2)          Removes non-printable characters
=TRIM(CLEAN(A2))    Does both at once — the standard data cleaning combo

Using =TRIM(CLEAN(A2)) on a whole column is often the first thing I do when working with imported data. It is quick, non-destructive, and fixes the majority of invisible text problems before they cause trouble downstream.

Measuring Length — LEN

LEN returns the number of characters in a text string — including spaces. It sounds trivial but it has several practical uses: validating that ID codes are the right length, finding cells where data was accidentally truncated, or checking that phone numbers have the right number of digits.

=LEN(A2)

=LEN("Hello")          →  5
=LEN("Hello World")    →  11   (space counts as a character)
=LEN("  North  ")      →  9    (spaces at the edges are counted too)

A common use: =IF(LEN(A2)=9, "Valid", "Check") — flags any ID that is not exactly 9 characters long. We will cover IF in the next lesson, but this gives you a preview of how these functions combine.

Extracting Text — LEFT, RIGHT, MID

These three functions extract a portion of a text string. They are the workhorses of text manipulation — you will use them constantly when splitting codes, extracting parts of product IDs, or pulling substrings out of combined fields.

=LEFT(text, num_chars)           Extract from the left
=RIGHT(text, num_chars)          Extract from the right
=MID(text, start_num, num_chars) Extract from the middle

MID needs three arguments — where to start (position number, counting from 1), and how many characters to extract. So =MID("GB-NORTH-2024", 4, 5) starts at character 4 (the N) and extracts 5 characters: NORTH.

Finding Text Position — FIND and SEARCH

When you need to extract text but the position is not fixed — for example, splitting a full name where the first name could be 3 characters or 10 — you need to find the position of a separator first. That is what FIND and SEARCH do.

=FIND(find_text, within_text, [start_num])
=SEARCH(find_text, within_text, [start_num])

=FIND(" ", "Sarah Johnson")    →  6   (position of the space)
=FIND("-", "GB-NORTH-2024")    →  3   (position of the first dash)

The difference between the two: FIND is case-sensitive. SEARCH is not. For most text splitting tasks, SEARCH is the safer choice.

Here is a real-world example — splitting "First Last" names into two separate columns. To extract the first name, you take everything to the left of the space. To extract the last name, you take everything to the right of it:

Full name in A2: "Sarah Johnson"

First name:  =LEFT(A2, SEARCH(" ", A2) - 1)
             LEFT of the space position, minus 1

Last name:   =MID(A2, SEARCH(" ", A2) + 1, LEN(A2))
             From 1 character after the space to the end

This combination of LEFT/MID + SEARCH + LEN is one of the classic text manipulation patterns in Excel. You will use it — or variations of it — regularly when cleaning imported data.

Combining Text — CONCATENATE and the & Operator

Joining text from multiple cells is called concatenation. There are two ways to do it — the & operator (faster to type) or the CONCAT function. Both produce the same result.

A2 = "Sarah"    B2 = "Johnson"    C2 = "North"

=A2&" "&B2                    →  Sarah Johnson
=CONCAT(A2, " ", B2)          →  Sarah Johnson
=A2&" "&B2&" ("&C2&")"        →  Sarah Johnson (North)
=TEXTJOIN(", ", TRUE, A2:C2)  →  Sarah, Johnson, North

TEXTJOIN is particularly useful when you want to join a range of cells with a separator — it takes a delimiter, a setting to ignore empty cells (TRUE/FALSE), and the range. Much cleaner than chaining a dozen & operators together for a long list.

Replacing and Substituting — SUBSTITUTE and REPLACE

These two functions let you swap text inside a string using a formula — useful when you want to clean up values without using Find & Replace manually, or when you need to build the replacement into a formula pipeline.

=SUBSTITUTE(text, old_text, new_text, [instance_num])
=REPLACE(old_text, start_num, num_chars, new_text)

=SUBSTITUTE("North-East Region", "-", " ")  →  North East Region
=SUBSTITUTE("2024/01/15", "/", "-")          →  2024-01-15
=SUBSTITUTE("aabbcc", "b", "x", 1)          →  aaxbcc  (replaces 1st b only)

=REPLACE("EMP00145", 1, 3, "STAFF")         →  STAFF00145
(replaces 3 characters starting at position 1)

The key difference: SUBSTITUTE finds and replaces by content (what the text says). REPLACE replaces by position (character number). Use SUBSTITUTE when you know what to replace. Use REPLACE when you know exactly where in the string to replace.

Converting Text — TEXT and VALUE

Sometimes you need to go in the opposite direction — converting a number or date into a formatted text string, or converting a text value that looks like a number back into an actual number.

=TEXT(value, format_code)

=TEXT(42000, "$#,##0")           →  "$42,000"
=TEXT(TODAY(), "dd mmm yyyy")    →  "08 Mar 2026"
=TEXT(0.1872, "0.0%")            →  "18.7%"

=VALUE("42000")                  →  42000  (converts text to number)
=VALUE("18.7%")                  →  0.187

TEXT is particularly useful when building dynamic labels for charts or report titles — for example: ="Sales as of "&TEXT(TODAY(),"dd mmm yyyy") produces a string like "Sales as of 08 Mar 2026" that updates automatically every day.

A Practical Data Cleaning Example

Let's put several of these functions together the way you would actually use them when cleaning an imported employee list. The raw data has mixed case, extra spaces, and full names in a single column. The goal is a clean table with separate first and last name columns, proper capitalisation, and trimmed whitespace:

Next up — Date Functions, where you will learn how Excel stores and calculates dates, and how to use TODAY, NOW, DATEDIF, EOMONTH, and more to work with time-based data.