Skip to content
Tutorials Center
My learning

Topics

Lesson 8 of 29 ยท Formulas and Functions

Working with Text

Clean and reshape text in Google Sheets with LEFT, RIGHT, MID, LEN, TRIM, UPPER, PROPER, CONCAT, TEXTJOIN, SUBSTITUTE and SPLIT, with live stand-ins.

Real spreadsheets are full of text: names, product codes, addresses, email lists. Data arrives messy, with extra spaces, odd capitalization and several facts glued into one cell. Google Sheets has a family of text functions that cut, clean, join and split strings so you can fix this with formulas instead of retyping.

Each function takes text in and returns new text (or a number, in the case of LEN). The original cell is never changed, which means you can always see what you started with. This lesson builds on Your First Formulas and uses the functions from SUM, AVERAGE, MIN, MAX and COUNT only lightly.

Cut, clean, join, split

Cut LEFT, RIGHT, MID take a slice.
Clean TRIM, UPPER, PROPER, SUBSTITUTE.
Join &, CONCAT and TEXTJOIN.
Split SPLIT breaks one cell into many.

Text goes in, new text comes out. The source cell stays untouched.

1 / 2

Cutting text: LEFT, RIGHT, MID and LEN

Imagine product codes like EU-2041-BLUE in cell A2. Text functions count characters from 1.

TEXT
=LEFT(A2, 2)       -> EU        (first 2 characters)
=RIGHT(A2, 4)      -> BLUE      (last 4 characters)
=MID(A2, 4, 4)     -> 2041      (start at 4, take 4)
=LEN(A2)           -> 12        (length, spaces included)

MID(text, start, length) is the most flexible: you give the position of the first character and how many to take. LEN is useful on its own to check that every code has the same length, or to compute how many characters to take. The result of these functions is text, even when it looks like a number. MID(A2, 4, 4) gives "2041"; wrap it in VALUE() or multiply by 1 when you need a real number. Entering and Editing Data explains why text and numbers behave differently.

To cut at a separator rather than a fixed position, find it first. FIND("-", A2) returns the position of the first hyphen (case-sensitive) and SEARCH does the same without caring about case. Then =LEFT(A2, FIND("-", A2) - 1) returns everything before the first hyphen.

Google Sheets itself cannot run on this page, so the formulas above are read-only. The TryIt blocks are JavaScript stand-ins that follow the same rules, so you can see how each function behaves.

JS Stand-in: LEFT, RIGHT, MID, LEN and FIND

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Cleaning text: TRIM, UPPER, LOWER and PROPER

Pasted data often has invisible problems. TRIM removes spaces at the start and end and shrinks runs of spaces inside the text to one. It is the first thing to try when two cells that look identical do not match in a lookup.

TEXT
=TRIM("   Ada    Lovelace ")   -> Ada Lovelace
=UPPER("ada")                  -> ADA
=LOWER("ADA")                  -> ada
=PROPER("ada LOVELACE")        -> Ada Lovelace

PROPER capitalizes the first letter of each word and lowercases the rest. It treats any non-letter as a word break, so names with apostrophes or hyphens and words such as "McDonald" can come out differently from what you want. Check the result on your real data before trusting it.

TRIM only handles ordinary spaces. Text copied from web pages may contain a non-breaking space, which TRIM leaves alone. CLEAN removes non-printing characters. For a specific stray character, use SUBSTITUTE.

Replacing text: SUBSTITUTE

SUBSTITUTE(text, search, replacement, [occurrence]) swaps one piece of text for another. Without the fourth argument it replaces every match; with it, only that numbered match.

TEXT
=SUBSTITUTE("2024/05/17", "/", "-")        -> 2024-05-17
=SUBSTITUTE("a-b-c-d", "-", "+", 2)        -> a-b+c-d
=SUBSTITUTE(A2, " ", "")                   -> removes all spaces

SUBSTITUTE is case-sensitive and matches plain text. For pattern matching you need REGEXREPLACE, which uses regular expressions. A related function, REPLACE(text, position, length, new), works by position instead of by content.

A classic use is counting words: the number of spaces plus one, after trimming. =LEN(TRIM(A2)) - LEN(SUBSTITUTE(TRIM(A2), " ", "")) + 1 subtracts the length without spaces from the length with spaces.

JS Stand-in: TRIM, PROPER and SUBSTITUTE

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Joining text: &, CONCAT and TEXTJOIN

The & operator glues pieces together: =A2 & " " & B2 turns "Ada" and "Lovelace" into "Ada Lovelace". CONCAT(a, b) does the same but accepts exactly two arguments, so & is usually handier. Numbers are converted to text as they join; use TEXT(value, "0.00") when you need a particular number format inside the sentence.

TEXTJOIN(delimiter, ignore_empty, text1, [text2, ...]) is the powerful one. It joins a whole range with a separator and can skip empty cells:

TEXT
=TEXTJOIN(", ", TRUE, A2:A6)    -> Ada, Grace, Linus
=TEXTJOIN(" | ", FALSE, A2:A6)  -> keeps empty slots as extra separators

Set ignore_empty to TRUE almost always, otherwise blank cells leave doubled commas. Combine it with a filtered list later, in Sorting and Filtering Data, to build a mailing string from only matching rows.

Splitting text: SPLIT

SPLIT(text, delimiter, [split_by_each], [remove_empty]) is the reverse of joining. It writes each piece into its own cell, spilling to the right of the formula cell. Those neighboring cells must be empty or you get an error.

TEXT
=SPLIT("red,green,blue", ",")        -> red | green | blue (three cells)
=SPLIT("a1b2c3", "123")              -> a | b | c   (each character is a delimiter)
=SPLIT("Ada Lovelace", " ")          -> Ada | Lovelace

By default every character in the delimiter string counts as a separate delimiter, because split_by_each is TRUE. Pass FALSE to treat the delimiter as one multi-character string. Consecutive delimiters create no empty cells unless you set remove_empty to FALSE. Split results are text, so numbers such as "7" may need VALUE. Combine SPLIT with INDEX to pick one piece: =INDEX(SPLIT(A2, "-"), 1, 2).

JS Stand-in: TEXTJOIN and SPLIT

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Separating names into first and last

A column of full names is one of the most common things to split. Sheets gives you two ways: a menu command that does it once, and a formula that stays up to date.

The menu way. Select the column, then choose Data > Split text to columns. A small Separator box appears at the bottom of the selection: pick Space, Comma, Semicolon, Period or Custom, or leave Detect automatically. The pieces are written over the cells to the right, so make sure those cells are empty. This is quick, but it is a one-time change: if the source names change, the pieces do not.

The formula way. Use SPLIT and pick the piece you want with INDEX, or spill them all with an array formula:

TEXT
=SPLIT(A2, " ")                               first, last (two cells)
=INDEX(SPLIT(A2, " "), 1)                     first name only
=INDEX(SPLIT(A2, " "), 2)                     second word
=ARRAYFORMULA(SPLIT(A2:A100, " "))            the whole column at once
=REGEXEXTRACT(A2, "(\S+)$")                  the LAST word (the surname) even with middle names
=REGEXEXTRACT(A2, "^(\S+)")                  the FIRST word

Real name lists are messy, and these are the cases to plan for:

Case Example What to do
Middle names or initials Mary Ann Smith Take the first word as the first name and the last word as the surname with REGEXEXTRACT
"Last, First" order Smith, Mary Split on ", " (with split_by_each set to FALSE) and swap the columns
Extra spaces " Mary Smith " Wrap the name in TRIM first: SPLIT(TRIM(A2), " ")
Titles and suffixes Dr. Mary Smith Jr. Remove them with SUBSTITUTE or a lookup list, then split
Wrong capitalization mary SMITH Wrap the result in PROPER
Large data sets 50,000 rows Use one ARRAYFORMULA rather than a formula per row; formulas per row are slower

A Google Workspace add-on from the Marketplace can also split names, including titles and suffixes. Add-ons need your permission to read the spreadsheet, so install only well-reviewed ones and never use them on files with confidential data without checking the publisher.

JS Stand-in: first and last names from messy full names

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The last line shows an edge case that no formula can solve by itself: a one-word name has no surname. Decide what your sheet should do (leave the surname empty, for example) and test with a handful of awkward names before you fill down 5,000 rows.

A worked example

You have full names in A2:A100 such as " grace HOPPER ". To get a tidy first name in B2, a last name in C2 and an email in D2:

TEXT
B2: =PROPER(INDEX(SPLIT(TRIM(A2), " "), 1, 1))
C2: =PROPER(INDEX(SPLIT(TRIM(A2), " "), 1, 2))
D2: =LOWER(B2 & "." & C2 & "@example.com")

Read from the inside out: TRIM cleans, SPLIT separates, INDEX picks a piece, PROPER fixes capitals. Copy the formulas down. When the formulas misbehave, Understanding Formula Errors helps you diagnose them.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does =MID("EU-2041-BLUE", 4, 4) return?

    MID starts at the 4th character (the 2) and takes 4 characters, giving 2041 as text.
  2. 2. Which function removes extra spaces at the start, end and between words?

    TRIM drops leading and trailing spaces and reduces runs of spaces inside the text to a single space.
  3. 3. Why is TEXTJOIN often preferred over CONCAT?

    CONCAT takes exactly two values. TEXTJOIN takes a delimiter and any number of values or ranges, and can ignore blanks.
  4. 4. Where does the output of SPLIT go?

    SPLIT spills each piece into neighboring cells to the right. If something is in the way, the formula returns an error.

Summary

  • LEFT, RIGHT and MID slice text by position; LEN measures it; FIND and SEARCH locate a separator.
  • TRIM, UPPER, LOWER, PROPER and SUBSTITUTE clean and fix text without changing the source cell.
  • & and CONCAT join two pieces; TEXTJOIN joins ranges with a delimiter and can skip blanks.
  • SPLIT spills pieces into cells on the right; results are text.
  • Text results that look like numbers are still text until you convert them.

Next: IF, AND, OR and IFS