10 Text Functions in Excel With Examples: Practical Formulas for Cleaning, Combining, and Analyzing Data

Tired of manually typing out full names or addresses in Excel? The CONCATENATE function lets you merge text from multiple cells into one with just a simple formula—no copy-pasting required.

This function combines up to 255 text strings (or cell references) into a single result. For example, if you have first names in column A and last names in column B, =CONCATENATE(A2, " ", B2) instantly creates "John Doe" in the output cell. Add a space (" ") or comma (,) between fields to control formatting. It’s perfect for generating labels, reports, or even email addresses from separate columns.

Small business owners and data analysts will love how this function automates repetitive tasks like creating mailing lists or standardized product codes. Give it a try next time you’re stuck merging scattered data! ⚡

Tired of manually copying ZIP codes or product codes from messy datasets? The LEFT and RIGHT functions in Excel are your secret weapon for instantly extracting specific characters from text strings.

Use LEFT to grab characters from the start (e.g., =LEFT(A1,5) extracts the first 5 digits from "123456789") or RIGHT for the end (e.g., =RIGHT(A1,4) pulls the last 4 characters). Perfect for pulling ZIP codes, phone extensions, or serial numbers from standardized formats without typing a single character.

Accountants, inventory managers, and data analysts will love how these functions save hours when cleaning up structured data. ⚡

When you need to pull specific segments from the middle of a text string—like extracting a serial number from a longer product code or isolating a model number from a full description—the MID function is your go-to tool. Unlike LEFT or RIGHT, which only grab characters from the start or end, MID lets you extract any substring by specifying the exact starting position and length.

The syntax is simple: =MID(text, startnum, numchars). For example, to extract "123" from "ABC123XYZ", use =MID(A1, 4, 3), where 4 is the starting position and 3 is the number of characters to pull. This function shines when working with standardized formats like invoices, inventory lists, or data imports where consistency is key.

Accountants validating batch numbers, IT teams parsing log files, or small business owners cleaning up customer data will find MID indispensable for precision extraction without manual copying. ⚡

When your data relies on precise character counts—like phone numbers (10 digits), product IDs (12 characters), or report word limits—the LEN function becomes your secret weapon. Unlike extraction tools, LEN doesn’t pull text; it counts it, making it perfect for validation tasks where length matters more than content.

Simply type =LEN(cell_reference) to return the exact character count, including spaces. Need to enforce 10-digit phone numbers? Combine LEN with an IF statement: =IF(LEN(A2)=10, "Valid", "Invalid"). For word counts, pair it with SUBSTITUTE to remove spaces first: =LEN(SUBSTITUTE(A1," ",""))-LEN(A1)+1. Works in all Excel versions, including Excel 365 and Google Sheets.

Accountants verifying invoice numbers, marketers checking ad copy lengths, or developers validating API keys—LEN saves hours of manual counting. ⚡

Struggling with extra spaces ruining your data’s cleanliness? The TRIM function is your secret weapon for instantly removing leading, trailing, and multiple internal spaces from text—no manual edits required.

Simply type =TRIM(cell_reference) to instantly clean up messy data, whether it’s from manual entry, imports, or copied text. It handles all three types of spaces automatically: leading (before text), trailing (after text), and extra spaces between words. For example, TRIM(" Hello World ") returns "Hello World" without any extra spaces.

This function is a lifesaver for anyone preparing data for analysis, reports, or exports. Whether you're cleaning up customer lists or fixing corrupted imports, TRIM ensures your text is ready for the next step. ✨

Frustrated by inconsistent text cases causing sorting headaches? The UPPER, LOWER, and PROPER functions are your secret weapons for instant standardization. Whether you're dealing with customer names, product codes, or database entries, these functions ensure uniformity in seconds—no manual edits required.

Use UPPER() to convert text to all caps (e.g., "New York" → "NEW YORK"), LOWER() for lowercase (e.g., "HELLO" → "hello"), and PROPER() to capitalize the first letter of each word (e.g., "john doe" → "John Doe"). They work seamlessly with TRIM() or SUBSTITUTE() for multi-step data cleanup. Best of all, they’re zero-config—just paste the formula and watch your data transform.

Accountants, marketers, and database admins will love these for generating reports or labels where case consistency matters. Try them on a messy dataset today—your future self will thank you! ✨

Tired of manually fixing typos or inconsistent abbreviations in your datasets? The SUBSTITUTE function automates these corrections in seconds, replacing old text with new text across entire columns—no copy-pasting required.

With the syntax =SUBSTITUTE(text, oldtext, newtext, [instancenum]), you can replace every occurrence of "N/A" with blanks, standardize "Dr." to "Doctor", or even remove special characters like "#" from product codes. The optional instancenum lets you target specific replacements (e.g., only the first "X" in a string).

Perfect for data analysts, accountants, or anyone wrestling with messy imports—this function turns hours of manual editing into a one-click fix. ✨

When you need to verify if critical characters exist in your data—like the '@' in an email or a ZIP code's hyphen—Excel’s FIND and SEARCH functions become indispensable. Unlike other text tools, these pinpoint exact positions or simply confirm presence, making them perfect for validation tasks.

The FIND function returns the starting position of a substring (case-sensitive) while SEARCH does the same but ignores case. For example, =FIND("@", A1) locates the '@' in an email address, while =SEARCH("zip", B1) finds "zip" regardless of uppercase. Both return errors if text isn’t found—use IFERROR to handle missing matches gracefully.

Data analysts and quality assurance teams rely on these to automate checks like email format validation or extract character positions for further processing. They’re the Swiss Army knife for precise text operations. ✨

Tired of CONCATENATE spitting out errors when your data has empty cells? The TEXTJOIN function is your upgrade—it combines text while skipping blanks and letting you control delimiters like commas or semicolons. Perfect for turning scattered data into clean, structured lists.

With TEXTJOIN(delimiter, ignore_empty, range), you gain three key advantages: custom separators (no more hardcoding commas), automatic blank-cell skipping, and support for Excel 2019 and Office 365. Need a comma-separated inventory list? TEXTJOIN handles it—no extra IF statements required.

This is the go-to for analysts, marketers, or anyone merging data from multiple columns into reports, invoices, or databases. It’s the modern, zero-fuss way to combine text. ✨

Ever imported a dataset from a PDF or database only to find Excel acting strangely—extra spaces, invisible symbols, or corrupted text? The CLEAN function is your secret weapon for scrubbing out those pesky non-printable characters that sneak in during imports.

This function removes ASCII characters 0-31 (like line breaks, tabs, or formatting codes) while preserving your visible text. For example, if you import a report with hidden formatting marks, =CLEAN(A1) instantly returns clean text. Combine it with TRIM for a double-cleanse against both spaces and invisible characters.

Perfect for data analysts, marketers, or anyone merging files from external sources. Just one formula keeps your datasets pristine before analysis—no manual scrubbing required! ✨