Text Functions in Excel: The Complete Guide

Text functions in Excel are the reason a data-cleaning job that used to take you all afternoon can be finished before your coffee gets cold. Picture this: you have just received a staff list exported from your HR system. Eight hundred rows. Every name is formatted as SMITH, JOHN in a single column. Your manager wants it split into first and last name columns by end of day. Without text functions, that is a long afternoon of copy-paste and manual typing. With them, it is three formulas and ten minutes.

Whether you are dealing with invisible spaces that break your VLOOKUPs, product codes you need to slice up for a pivot table, or customer names imported in the wrong case, the Excel text formula toolkit has you covered. This guide covers every essential function, organised around three practical jobs: extracting the text you need, cleaning the text that is broken, and combining text to build something new.

By the end, you will have a clear mental model for choosing the right function — not just a list of syntax to memorise.

What Are Text Functions in Excel and Why Do They Matter

Numbers in Excel are straightforward. Add them, average them, look them up. Text is a different story. It comes from everywhere — exported from a CRM, pasted from a website, typed by twelve different people with twelve different habits. It arrives inconsistent, padded with spaces you cannot see, capitalised randomly, and formatted in ways that make your formulas fall apart.

Text functions in Excel give you a way to deal with all of that without touching the original data. You can measure text, slice it, clean it, reformat it, and stitch pieces together — all with a formula in a clean helper column. Once you get comfortable with these tools, you will stop thinking of messy data as a problem and start thinking of it as something you know exactly how to fix.

When You Actually Need a Text Formula

You have probably seen at least one of these situations before:

• A name list formatted as Last, First that needs to become First Last
• A column of product codes like CAT-SHOES-42-BLK where you only need one segment
• A VLOOKUP returning errors even though the values look identical in both columns
• A report where you need a cell to read “Sales as of 27 April 2026” using a live date
• Phone numbers entered in five different formats across five hundred rows

Any of those ring a bell? Keep reading.

The Three Jobs of Excel Text Functions

Rather than listing twenty functions in alphabetical order, here is a framework that makes the whole toolkit easier to use. Every text function in Excel does one of three jobs:

• Extract — Pull a specific piece of text out of something larger. LEFT, RIGHT, MID, FIND, and SEARCH live here.
• Clean — Fix text that arrived broken, padded, or formatted incorrectly. TRIM, LEN, UPPER, LOWER, and PROPER are your tools.
• Combine — Build a new text string by joining pieces together. CONCATENATE, CONCAT, the ampersand shortcut, and TEXTJOIN do this work.

Extract: Pull the Text You Need

Excel LEFT and RIGHT Functions

LEFT grabs a number of characters from the start of a text string. RIGHT grabs from the end. The choice between them comes down to where your data lives in the string.

Excel MID Function

MID pulls characters from somewhere in the middle of a string. You tell it where to start and how many characters to take. It is the go-to formula for structured text like reference numbers, product codes, or ID strings where the piece you need always sits at a predictable position.

Excel FIND and SEARCH Functions

Both FIND and SEARCH locate the position of one piece of text inside another and return a number. The important difference: FIND is case-sensitive. SEARCH is not. SEARCH also supports wildcards; FIND does not.

Clean: Fix Messy or Inconsistent Data

Excel TRIM Function — The Most Underused Formula

Here is the thing about data that comes from outside your spreadsheet: it almost always carries hidden baggage. When someone exports a CSV from a CRM, copies a table from a website, or sends you data from a legacy system, the text in those cells often has extra spaces hiding at the start, the end, or between words. You cannot see them. They are invisible. But Excel sees them — and they cause real problems.

Think of TRIM like a firm handshake for your data. It removes every extra space, leaves exactly one space between each word, and returns clean text you can actually work with.

Excel LEN Function

LEN counts the number of characters in a text string, including every space. You will use it in two ways: as a diagnostic tool to measure string length and flag unexpected values, and as a helper inside other formulas to make extractions dynamic.

UPPER, LOWER, and PROPER Functions

Three functions, one job: standardise how text is capitalised. You will reach for these most often when preparing data for a VLOOKUP where case inconsistency might cause failures, or when formatting names for a mail merge.

Combine: Build New Text from Existing Data

Excel CONCATENATE Function and Why You Should Upgrade

CONCATENATE joins text strings together. If A2 contains “John” and B2 contains “Smith”, the formula below returns “John Smith”. The space between the quote marks is a literal space character being inserted between the two values.

TEXTJOIN: The Function CONCATENATE Should Have Been

TEXTJOIN joins a range of values using a delimiter you choose, and lets you decide whether to skip blank cells. It is far more powerful than CONCATENATE for anything involving a range.

TEXT Function: Formatting Numbers as Text

The TEXT function converts a number or date value into a text string formatted exactly the way you specify. This is most useful when you need to embed a number or date inside a sentence. Without TEXT, concatenating a date cell directly gives you a meaningless serial number like 46218.

Combining Text Functions in Excel: Where the Real Power Is

Individual functions are useful. Combined, they solve problems that would otherwise take hours. Here are three real-world examples that show the toolkit in action.

Use Case 1: Split Full Names into First and Last

Your HR export has names formatted as SMITH, JOHN in a single column. You need them separated.

Use Case 2: Extract a Product Category from a SKU Code

Your SKUs follow the format CATEGORY-PRODUCTNAME-SIZE-COLOUR. You need just the category. Because the category always sits before the first dash, one formula handles every row cleanly — no matter how long or short the category code is.

Use Case 3: Fix VLOOKUP Errors Caused by Hidden Spaces

Your VLOOKUP is returning errors. Both columns look identical. You have checked the spelling twice. The culprit is almost always hidden spaces. Here is how to diagnose and fix it. For a full VLOOKUP walkthrough, see Lesson 15: INDEX & MATCH in Excel on XplorExcel.

Quick Reference: Excel Text Functions Cheat Sheet

Try It Yourself

Frequently Asked Questions

What is the difference between FIND and SEARCH in Excel?

FIND is case-sensitive and does not accept wildcard characters. SEARCH is not case-sensitive and does accept wildcards. Use FIND when the exact case of the character matters. Use SEARCH when you want to match regardless of case or when you need a wildcard pattern.

How do I remove spaces in Excel?

TRIM is the function for this. It removes leading spaces, trailing spaces, and any runs of double spaces within the string, handling the vast majority of space problems from imported data. If you are dealing with non-breaking spaces copied from a website, those require SUBSTITUTE with CHAR(160) as well, since TRIM does not catch that particular character.

What replaced CONCATENATE in Excel?

CONCAT and TEXTJOIN are the modern replacements, both available in Excel 2019 and Excel 365. CONCAT works identically to CONCATENATE but also accepts cell ranges. TEXTJOIN adds a delimiter argument and the ability to ignore blank cells, making it far more powerful for range-based joining.

Can I combine text and numbers in Excel?

Yes, and the TEXT function is the key to doing it well. Without TEXT, concatenating a number or date cell directly gives you the raw stored value — a serial number for dates, and an unformatted integer for numbers. Use TEXT to specify exactly how you want the value to appear before joining it with the rest of your string.