String Functions in Google Sheets: A Practical Guide

How-to basics · Updated November 22, 2026

Google Sheets offers a robust set of string functions—built-in formulas that manipulate text—that let you clean, extract, combine, and reformat data directly in your spreadsheet. The most essential ones are CONCATENATE (or the & operator) to join text, LEFT, RIGHT, and MID to extract parts, LEN to count characters, FIND and SEARCH to locate text, and SUBSTITUTE and REPLACE to change text. This guide explains how these functions work, when to use each, and common pitfalls, with real examples you can copy.

Why String Functions Matter

Spreadsheets rarely contain perfectly formatted text. Names arrive with extra spaces, phone numbers mix formats, and product codes hide inside longer strings. String functions let you automate the cleanup and transformation of this text without manual editing. For example, if you have a column of full names like “John A. Smith” and need just the first name, LEFT combined with FIND can extract it in seconds. These functions are the building blocks for data cleaning, report formatting, and even simple text parsing.

The Core String Functions and How They Work

Here are the most frequently used string functions, with their syntax and a concrete example. In all examples, assume cell A1 contains “Hello World” unless stated otherwise.

  • LEN(text) – Returns the number of characters. =LEN(A1) returns 11 (the space counts).
  • LEFT(text, [num_chars]) – Extracts characters from the left. =LEFT(A1,5) returns “Hello”. If you omit num_chars, it returns the first character.
  • RIGHT(text, [num_chars]) – Extracts from the right. =RIGHT(A1,5) returns “World”.
  • MID(text, start_position, num_chars) – Extracts from the middle. =MID(A1,7,5) returns “World” (starts at character 7).
  • CONCATENATE(text1, [text2], ...) – Joins text. =CONCATENATE("Hello"," ","World") returns “Hello World”. The ampersand operator & does the same: ="Hello"&" "&"World".
  • FIND(search_text, within_text, [start_num]) – Returns the position of a substring, case-sensitive. =FIND("World",A1) returns 7.
  • SEARCH(search_text, within_text, [start_num]) – Like FIND but not case-sensitive and allows wildcards. =SEARCH("world",A1) also returns 7.
  • SUBSTITUTE(text, old_text, new_text, [instance_num]) – Replaces occurrences of text. =SUBSTITUTE(A1,"World","Earth") returns “Hello Earth”. Use the optional instance_num to replace only a specific occurrence.
  • REPLACE(old_text, start_num, num_chars, new_text) – Replaces a portion of text based on position. =REPLACE(A1,7,5,"Earth") returns “Hello Earth”.
  • TRIM(text) – Removes leading, trailing, and extra internal spaces. =TRIM(" Hello World ") returns “Hello World”.
  • UPPER(text), LOWER(text), PROPER(text) – Change case. =UPPER(A1) gives “HELLO WORLD”.
  • TEXT(value, format) – Converts a number to text in a specified format. =TEXT(1234.5,"$#,##0.00") returns “$1,234.50”.

Combining String Functions for Real Tasks

Individually, these functions are simple; together they solve messy problems. For instance, to extract the first name from a full name in cell A2 (assuming a space separates names):

  • Find the position of the first space: =FIND(" ", A2) returns, say, 6.
  • Use LEFT to get everything before that: =LEFT(A2, FIND(" ", A2)-1).

If you need the last name (assuming only one space), use =RIGHT(A2, LEN(A2)-FIND(" ", A2)). For a middle initial, combine MID and FIND. You can also nest functions inside each other, as shown, to perform multi-step transformations in a single cell.

Choosing the Right Function: A Decision Guide

With many functions available, picking the right one depends on what you know about the text. Use this quick guide:

  • If you know the exact position (e.g., always the first 3 characters), use LEFT or MID with a fixed number.
  • If you know a delimiter (space, comma, hyphen), use FIND or SEARCH to locate it, then combine with LEFT/RIGHT/MID.
  • If you need to replace a specific text pattern (e.g., change “Mr.” to “Mister”), use SUBSTITUTE.
  • If you need to fix spacing or case, use TRIM, UPPER, LOWER, or PROPER.
  • If you are joining multiple cells, use CONCATENATE or the & operator. For a range, consider TEXTJOIN (available in Google Sheets) which lets you specify a delimiter and ignore empty cells.

Common Problems and How to Avoid Them

String functions often trip up users with a few recurring issues:

  • Case sensitivity: FIND is case-sensitive; SEARCH is not. If you’re looking for “world” in “Hello World”, FIND returns an error, but SEARCH works. Use SEARCH unless case matters.
  • Spaces and non-printing characters: Data imported from other systems may have trailing spaces or line breaks. TRIM removes spaces but not line breaks; use CLEAN to remove non-printable characters (like line breaks).
  • Errors when text is not found: FIND and SEARCH return a #VALUE! error if the search text is absent. Wrap them with IFERROR to handle gracefully, e.g., =IFERROR(LEFT(A2, FIND(" ", A2)-1), A2).
  • Numbers stored as text: Functions like LEFT work on text, but if a cell contains a number, it may be treated as a number. Use TEXT to convert if needed.
  • Locale differences: Google Sheets uses commas as argument separators in most locales, but some European locales use semicolons. Check your spreadsheet locale under File > Settings.

String Functions vs. Other Text Tools

Google Sheets also offers menu-based text tools that can complement or replace formulas. The Split text to columns feature (Data > Split text to columns) is perfect for one-time splits by a delimiter—it’s faster than writing formulas. Find and replace (Ctrl+H on Windows, Cmd+Shift+H on Mac) can substitute text across a range, but it’s not dynamic; if the source changes, you must redo it. Formulas are dynamic—they update automatically when the source text changes. For complex transformations, formulas are more flexible, but for quick cleanups, the menu tools are simpler.

Frequently Asked Questions

How do I combine text from multiple cells with a separator?
Use TEXTJOIN, which is available in Google Sheets. For example, with names in A2:A5, =TEXTJOIN(", ", TRUE, A2:A5) joins them with a comma and space, ignoring empty cells. The second argument (TRUE) means ignore blanks.

What’s the difference between FIND and SEARCH?
FIND is case-sensitive and does not allow wildcards. SEARCH is case-insensitive and supports wildcards like ? (any single character) and * (any sequence). For example, =SEARCH("w*", A1) finds the first “w” in “Hello World” and returns 7.

Why does my formula return #VALUE! error?
This often happens when FIND or SEARCH can’t locate the text, or when you use a function with a number where text is expected. Check the arguments, ensure the search text exists, and wrap with IFERROR to handle missing values.

How do I extract text between two delimiters?
Assume cell A1 contains “[start]data[end]”. To get “data”, use =MID(A1, FIND("[", A1)+1, FIND("]", A1)-FIND("[", A1)-1). This finds the first bracket, then the second, and calculates the length between them.

Can I use string functions inside other functions like IF?
Yes, string functions can be nested anywhere. For example, =IF(LEFT(A1,1)="A", "Starts with A", "Other") checks the first character. This is powerful for conditional formatting or data validation.

Are these functions the same as in Excel?
Most are identical, but Google Sheets includes TEXTJOIN and SPLIT (which splits text into separate cells) that Excel lacks natively (Excel has TEXTJOIN in newer versions). Also, Google Sheets’ REGEXEXTRACT, REGEXREPLACE, and REGEXMATCH provide advanced pattern matching not available in Excel’s standard functions.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.