How To SheetsHow To Sheets
Formulas and Functions

Google Sheets Text Functions: A Practical Guide for 2026

Master Google Sheets text functions with practical examples for cleaning, extracting, and formatting strings. Learn LEFT, RIGHT, MID, LEN, TRIM, UPPER/LOWER/PROPER, TEXTJOIN, SUBSTITUTE, REGEXEXTRACT and REGEXREPLACE for reliable data prep.

How To Sheets
How To Sheets Team
·5 min read
Wrap-Text-Google-SheetsSearch-Google-SheetsFind-Duplicates-Google-SheetsTemplates-Google-SheetsGoogle-Sheets-Vs-Excel
Text Functions in Sheets - How To Sheets
Photo by Firmbee.comvia Pexels
Quick AnswerFact

Google Sheets text functions let you clean, extract, format, and combine strings inside cells. This guide covers core tools like LEFT, RIGHT, MID, LEN, TRIM, and case helpers (UPPER/LOWER/PROPER), plus concatenation with TEXTJOIN and pattern matching with REGEXREPLACE and REGEXEXTRACT. By mastering these functions, you can normalize data, prepare imports, and generate consistent text outputs across projects. According to How To Sheets, practical examples accelerate learning and adoption.

Introduction to Google Sheets Text Functions

Google Sheets text functions give you power to clean, extract, format, and combine strings across cells. They help standardize datasets and prepare data for import, dashboards, and reporting. In this guide, we cover core functions such as LEFT, RIGHT, MID, LEN, TRIM, and the case helpers UPPER, LOWER, and PROPER, plus modern regex tools. The How To Sheets team emphasizes practical examples for faster learning and adoption.

Excel Formula
=LEN(A2)

Explanation: LEN returns the number of characters in A2. This is useful for validating input length and for dynamic text slicing.

Excel Formula
=TRIM(B2)

Explanation: TRIM removes extra spaces from both ends, consolidating internal spaces where needed.

Basic Text Extraction: LEFT, MID, RIGHT

Text extraction lets you grab leading or trailing segments from a string or pull substrings by position and length. The functions LEFT, MID, and RIGHT form a simple toolkit for parsing codes, identifiers, or names. Use them in combination with LEN for dynamic lengths.

Excel Formula
=LEFT(A2, 4)

Explanation: LEFT takes the first 4 characters of A2.

Excel Formula
=MID(A2, 3, 5)

Explanation: MID extracts a 5-character slice starting at character 3.

Excel Formula
=RIGHT(A2, 3)

Explanation: RIGHT returns the last 3 characters of A2.

Case and Search: UPPER, LOWER, PROPER, FIND, and SEARCH

Normalizing text and locating substrings are common preprocessing steps. UPPER, LOWER, and PROPER standardize case. FIND and SEARCH locate substrings; FIND is case-sensitive, while SEARCH is not. Combine these with IF and LEN to build robust validation checks.

Excel Formula
=UPPER(C2)

Explanation: Converts C2 to all caps for consistency.

Excel Formula
=LOWER(C2)

Explanation: Converts C2 to lower case for case-insensitive comparisons.

Excel Formula
=PROPER(D2)

Explanation: Capitalizes the first letter of each word in D2.

Excel Formula
=FIND("cat", A2)

Explanation: Returns the starting position of the exact substring 'cat' (case-sensitive).

Concatenation and Joining: CONCAT, CONCATENATE, TEXTJOIN

Merging multiple text values is a frequent task when shaping results or creating display labels. CONCAT and CONCATENATE offer two ways to join two or more strings, while TEXTJOIN can merge many cells with a delimiter and optionally ignore blanks.

Excel Formula
=CONCAT(A2, B2)

Explanation: Joins A2 and B2 directly.

Excel Formula
=CONCATENATE(A2, " ", B2)

Explanation: Adds a space between two fields for readability.

Excel Formula
=TEXTJOIN(" ", TRUE, A2:B2)

Explanation: Merges a range with a space delimiter, skipping blanks when TRUE.

Substitution and Pattern Matching: SUBSTITUTE, REPLACE, REGEXREPLACE

Substitution functions modify existing text, while replacement functions allow positional edits. SUBSTITUTE replaces all occurrences of old with new, REPLACE edits a slice by position, and REGEXREPLACE uses patterns to transform text at scale. This is powerful for data cleaning, masking, and standardization.

Excel Formula
=SUBSTITUTE(A2, "old", "new")

Explanation: Replaces every occurrence of old with new in A2.

Excel Formula
=REPLACE(A2, 3, 2, "XX")

Explanation: Replaces 2 characters starting at position 3 with XX.

Excel Formula
=REGEXREPLACE(A2, "[^0-9]", "")

Explanation: Replaces any non-digit character with nothing.

Regex Extraction: REGEXEXTRACT, REGEXREPLACE

Regex-based functions extract or transform text by pattern. REGEXEXTRACT pulls a matching group, while REGEXREPLACE can substitute pattern matches with a replacement string. These are ideal for extracting IDs, emails, or domain pieces from messy data.

Excel Formula
=REGEXEXTRACT(A2, "\([A-Za-z]+\)")

Explanation: Returns the first sequence of letters found in A2.

Excel Formula
=REGEXREPLACE(A2, "[^0-9]", "")

Explanation: Strips non-digit characters, leaving only digits.

Formatting with TEXT: Numbers and Dates in Text Outputs

TEXT formats transform numbers and dates into human-friendly strings. Using TEXT you can enforce consistent date formats, decimal precision, and percentages for display or export. This is especially helpful when combining numeric data with labels or in dashboards.

Excel Formula
=TEXT(A2, "mm/dd/yyyy")

Explanation: Formats a date value in A2 as MM/DD/YYYY.

Excel Formula
=TEXT(A2, "0.00")

Explanation: Rounds A2 to two decimals for display.

Excel Formula
=TEXT(A2, "0%")

Explanation: Formats A2 as a percentage.

Practical patterns and pitfalls with text functions

Tips for applying text functions across columns, using ARRAYFORMULA for large ranges, and testing formulas with sample data to catch edge cases. Common pitfalls include hidden spaces, non-breaking spaces, and locale-dependent decimal separators. Start with simple tests and expand to regex when patterns become too complex.

Excel Formula
=ARRAYFORMULA(TEXTJOIN(", ", TRUE, A2:A100))

Explanation: Combines all non-blank cells in A2:A100 with comma-space separators in a single array formula.

Steps

Estimated time: 20-35 minutes

  1. 1

    Open a test sheet

    Create a sheet with sample text data in several columns to practice.

    Tip: Use a clean dataset to avoid hidden characters.
  2. 2

    Apply LEFT/MID/RIGHT

    Experiment with leading/trailing extractions and dynamic lengths.

    Tip: Comment each formula inline to track intent.
  3. 3

    Normalize case

    Convert sample text to upper, lower, and proper case to standardize.

    Tip: Consider locale differences if your data includes diacritics.
  4. 4

    Join text values

    Use CONCAT, CONCATENATE, and TEXTJOIN to create display labels.

    Tip: Prefer TEXTJOIN for ranges with blanks.
  5. 5

    Explore regex

    Use REGEXEXTRACT and REGEXREPLACE for pattern-based tasks.

    Tip: Test patterns with multiple test strings.
Pro Tip: Use TRIM early to remove extra spaces before parsing text.
Warning: REGEX patterns can be brittle; test with real-world data and guard with IFERROR.
Note: TEXT functions are locale-dependent; dates and numbers may format differently by region.

Prerequisites

Required

Optional

  • Optional regex basics for advanced patterns
    Optional
  • Keyboard shortcuts cheat sheet
    Optional

Keyboard Shortcuts

ActionShortcut
CopyCopy selected cell or formulaCtrl+C
PastePaste into a cell or rangeCtrl+V
Fill downFill formulas downwardCtrl+D
UndoUndo last actionCtrl+Z

FAQ

What is the difference between FIND and SEARCH?

FIND is case-sensitive, returning the position of the substring if found. SEARCH ignores case differences, making it useful for flexible matches. Both return #VALUE! when the substring isn’t found, so wrap in IFERROR if needed.

Find is case-sensitive, while Search ignores case. Use the one that matches your needs, and handle not-found cases in your formulas.

Can text functions operate on entire columns?

Yes. Use ARRAYFORMULA to apply a function across a whole column, e.g., =ARRAYFORMULA(UPPER(A2:A)). This avoids writing formulas in every row and keeps results aligned.

Absolutely. Use ARRAYFORMULA to extend text functions down a column.

How do I trim extra spaces across a column?

Apply TRIM to a range with ARRAYFORMULA, e.g., =ARRAYFORMULA(TRIM(A2:A)). This removes leading and trailing spaces for all rows.

Trim spaces across the whole column with ARRAYFORMULA.

How can I convert numbers stored as text to numeric values?

Use VALUE to convert text to numbers, or apply a double negative like --A2 to coerce types. Then you can perform arithmetic normally.

Convert text to numbers with VALUE or a double unary.

Are there limitations to using REGEX in Google Sheets?

REGEX is powerful but can be slow on very large ranges. Break complex tasks into smaller steps and test patterns on subsets before scaling up.

Regex is powerful but watch performance on big datasets.

The Essentials

  • Learn core text functions for cleaning data
  • Extract, join, and format strings efficiently
  • Use REGEX for pattern-based transformations
  • Test formulas across several sample rows
  • Leverage ARRAYFORMULA for column-wise operations

Related Articles

← More in Formulas and Functions