SheetSense

Extract part of a text with MID

MID returns a chosen number of characters from the middle of a text, starting at a position you give it.

fx
=MID(A2,5,3)

The sheet: Column A holds codes such as NYC-204-X. You want the three characters that start at position 5, which is 204.

How it works, step by step

  1. 01A2 is the text.
  2. 025 is the position of the first character to take. The first character of the text is position 1, so in NYC-204-X position 5 is the 2.
  3. 033 is how many characters to return.
  4. 04The result for NYC-204-X is 204.

The same logic in four tools

Excel
=MID(A2,5,3)
Google Sheets
=MID(A2,5,3)
SQL
SELECT SUBSTRING(code FROM 5 FOR 3) AS part
FROM items;
Python (pandas)
df["part"] = df["code"].str[4:7]  # 0-based: position 5 starts at index 4

Mistakes that break it

  • !Position counts start at 1 in sheets and SQL but at 0 in pandas.
  • !MID returns text, even for digits. Wrap it in VALUE if you need a number.
  • !If the start is beyond the end of the text, MID returns an empty string rather than an error.
  • !When codes vary in length, combine MID with FIND to locate the delimiter instead of hard-coding positions.

Common questions

How do I take the first or last characters?
Use LEFT(A2,3) for the first three and RIGHT(A2,3) for the last three.
How do I extract between two delimiters?
Use TEXTAFTER and TEXTBEFORE in Microsoft 365, or MID with FIND in older versions.

Got a version of this with your own ranges?

Paste it into the free translator and get the same breakdown for your exact formula. To run it against a real file, upload the CSV and describe the formula you need in a sentence.