Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 93 ยท Text, Date and Time

Text Functions: LEFT, RIGHT, MID, LEN, TRIM

Extract, measure and clean text in Excel with LEFT, RIGHT, MID, LEN, FIND, SEARCH, TRIM, CLEAN, UPPER, LOWER, PROPER, SUBSTITUTE and REPLACE.

Spreadsheets hold far more text than numbers: names, product codes, e-mail addresses, addresses, notes. Real data is rarely tidy. A code such as SKU-1042-BLU hides three facts in one cell, a name arrives with extra spaces, and an e-mail address needs to be split at the @. Text functions let you take such values apart, measure them and clean them with formulas, so the result stays correct when the data changes.

In this lesson you will meet the everyday text toolkit: LEFT, RIGHT, MID, LEN, FIND and SEARCH, TRIM, CLEAN, the case functions, SUBSTITUTE and REPLACE. Excel cannot run on this site, so a small JavaScript engine evaluates real formula strings for you, and a Python block shows the same ideas with str methods. Joining and splitting are the subject of the next lesson, Joining and Splitting Text.

Four jobs for text functions

Extract LEFT, RIGHT, MID cut out a piece.
Measure LEN, FIND, SEARCH count and locate.
Clean TRIM and CLEAN remove junk.
Change SUBSTITUTE and REPLACE edit text.
1 / 2

Extracting: LEFT, RIGHT and MID

  • LEFT(text, [num_chars]) returns characters from the start. If you omit num_chars it returns one.
  • RIGHT(text, [num_chars]) returns characters from the end.
  • MID(text, start_num, num_chars) returns characters from the middle. start_num counts from 1, and both other arguments are required.

For the code SKU-1042-BLU in cell A3:

TEXT
=LEFT(A3, 3)      SKU
=MID(A3, 5, 4)    1042
=RIGHT(A3, 3)     BLU

If you ask for more characters than exist, you simply get all that is left. If num_chars is negative, you get #VALUE!.

An important detail: the result is always text, even when it looks like a number. MID(A3, 5, 4) gives the text "1042". That is fine for display, but SUM ignores text and arithmetic on it may need VALUE(...). The conversion problems are covered in TEXT, VALUE and Number-as-Text Problems.

LEN(text) counts characters, including spaces and punctuation. A number is treated as its displayed digits, so LEN(12345) is 5.

FIND(find_text, within_text, [start_num]) and SEARCH(find_text, within_text, [start_num]) both return the position where a piece of text starts, counted from 1. They differ in two ways:

FIND SEARCH
Case Case-sensitive Not case-sensitive
Wildcards (?, *) Not allowed (they are plain characters) Allowed
Not found #VALUE! #VALUE!

The optional start_num says where to begin looking, so you can find the second hyphen by starting just after the first one: FIND("-", A3, FIND("-", A3) + 1).

Position finders and extractors work as a pair. To get the part of an e-mail address before the @:

TEXT
=LEFT(A4, FIND("@", A4) - 1)

FIND returns the position of @, and you ask LEFT for one character fewer. For the part after it, use MID(A4, FIND("@", A4) + 1, 100) with a number large enough for any address. (Excel 365 also has TEXTBEFORE and TEXTAFTER, which do this directly; see the next lesson.)

Cleaning: TRIM, CLEAN and the case functions

Data pasted from other systems often carries invisible problems. These functions fix the common ones:

  • TRIM(text) removes spaces at the start and end and reduces every run of spaces inside the text to one space. It works only on the ordinary space (character 32).
  • CLEAN(text) removes the non-printing characters with codes 0 to 31, such as tabs and line feeds from imported text.
  • UPPER, LOWER and PROPER change case. PROPER capitalizes the first letter of each word and every letter that follows a non-letter, which gives O'Neil and also Mcdonald'S, so check names afterwards.

A frequent trap is the non-breaking space, character 160, which web pages and some exports use. TRIM leaves it alone. Replace it first with SUBSTITUTE(A1, CHAR(160), " "), then trim. LEN is the fastest way to detect such hidden characters: if a cell that looks like Ada has length 4, something is lurking.

Changing: SUBSTITUTE and REPLACE

SUBSTITUTE(text, old_text, new_text, [instance_num]) swaps content. It is case-sensitive and replaces every match unless you name one occurrence with instance_num. REPLACE(old_text, start_num, num_chars, new_text) swaps by position, no matter what the characters are.

TEXT
=SUBSTITUTE(A3, "-", "/")      SKU/1042/BLU
=SUBSTITUTE(A3, "-", "/", 2)   SKU-1042/BLU
=REPLACE(A3, 5, 4, "9999")     SKU-9999-BLU
=SUBSTITUTE(" a b ", " ", "")  ab   (removes every space)

A handy counting trick uses LEN and SUBSTITUTE together: the number of hyphens in A3 is LEN(A3) - LEN(SUBSTITUTE(A3, "-", "")).

Run it: formulas in a JavaScript engine

The engine below parses a sheet of cells, evaluates formulas that start with = and implements the functions from this lesson with the rules described above. It is a stand-in: it is small, handles only single-sheet formulas, and treats blank cells as empty text. The cell A6 is built with CHAR(160) to simulate a non-breaking space.

JS Text formulas on a mini sheet

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

No output.

Read three results closely. FIND("sku", A3) fails with #VALUE! because FIND is case-sensitive, while SEARCH("sku", A3) returns 1. LEN(TRIM(A6)) stays the same as LEN(A6) because TRIM ignores the non-breaking space, and the SUBSTITUTE version replaces it with a normal space. And SUBSTITUTE(A3, "sku", "x") changes nothing, since SUBSTITUTE is case-sensitive too.

The same ideas in Python

Python strings have close equivalents. Python counts positions from 0 and slices end before the last index, which is the main difference to keep in mind when you move between the two worlds.

Python Excel text functions as Python str methods

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

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The "2nd only" line shows that Python's replace count works from the left, so replacing only the second occurrence needs a small workaround, whereas Excel's instance_num does it directly.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A1 contains the text ABCDEFG. What does =MID(A1, 3, 2) return?

    MID starts at character 3 (the letter C) and takes 2 characters, so the result is CD.
  2. 2. What is the key difference between FIND and SEARCH?

    FIND treats upper and lower case as different and takes ? and * literally. SEARCH ignores case and treats ? and * as wildcards.
  3. 3. A cell looks like "Ada" but LEN says 4, and TRIM does not change it. What is the likely cause?

    TRIM removes only the ordinary space (character 32). Replace CHAR(160) with SUBSTITUTE first.
  4. 4. What does =SUBSTITUTE("a-b-c", "-", "+", 2) return?

    The fourth argument says to replace only the second occurrence of the hyphen, so the result is a-b+c.
  5. 5. =MID(A3, 5, 4) returns 1042 from SKU-1042-BLU. What type is the result?

    Text functions return text. Use VALUE(...) or multiply by 1 when you need a real number.

Summary

  • LEFT, RIGHT and MID extract text by position; LEN measures it.
  • FIND (case-sensitive) and SEARCH (case-insensitive, wildcards) locate text; combine them with the extractors to split values.
  • TRIM fixes ordinary spaces, CLEAN removes control characters, and character 160 needs SUBSTITUTE.
  • SUBSTITUTE replaces by content (all matches or one instance); REPLACE replaces by position.
  • Text functions return text, so convert with VALUE when you need numbers.

Next: Joining and Splitting Text.