Pulling From the Middle With MID

LEFT and RIGHT handle the edges of text, but what about characters buried in the middle?

Think of a code like US-4471-X where the four-digit number sits between two dashes. You cannot reach it from either end alone. For this you need the MID function.

MID needs three pieces of information:

• The text (or a cell)
• The start position — which character to begin at
• How many characters to grab

Positions are counted from the left, starting at 1. So character 1 is the first letter, character 2 is the second, and so on.

Take the word Spreadsheet. To pull out read, you start at position 3 (the letter r) and grab 4 characters.

MID("Spreadsheet", 3, 4) returns read. Counting carefully: S(1) p(2) r(3) e(4) a(5) d(6). Starting at 3 and taking 4 gives r-e-a-d.

Suppose A2 holds the code US-4471-X and you want the middle number 4471.

The number starts at position 4 (right after US-) and is 4 characters long. This returns 4471, pulled cleanly from the center of the code.

The most common MID mistake is miscounting the start position. Remember that every character counts — including dashes, spaces, and symbols.

In US-4471-X: U is 1, S is 2, the dash is 3, then 4 is the first digit. If you forget to count the dash, you start one position too early and get a wrong result.

If you only want one character, set the count to 1. To pull the third character of whatever is in A2:

MID(A2, 3, 1) returns just that one character. This is useful for checking a flag built into a code, like a status letter at a fixed position.

What if you want everything from a start point onward but do not know the exact length? Just pass a count that is safely larger than any string.

MID(A2, 4, 100) grabs from position 4 to the end. Like LEFT and RIGHT, MID never errors when you ask for more characters than exist — it simply stops at the end.

Hard-coding a start position only works when the format is fixed. When it varies, use FIND or SEARCH to locate a marker.

If a name is stored as Last, First in A2, FIND(", ", A2) returns the position of the comma. Feeding that into MID lets you extract the first name no matter how long the last name is.

Both FIND and SEARCH locate text inside other text and return its position number. The difference:

• FIND is case-sensitive
• SEARCH is not, and it allows wildcards

Use SEARCH when you do not care about capitalization. Both pair naturally with MID to make extraction flexible.

Imagine email addresses in column A and you want just the domain. The domain starts right after the @ sign.

FIND("@", A2) finds the @ position, you add 1 to skip it, and MID takes a large count to reach the end. The result is everything after the @, such as coddykit.com.

To pull text sitting between two known characters, find both positions and let MID grab what is in between.

In US-4471-X, the first dash and second dash bracket the number. FIND("-", A2) locates the first dash. Adding 1 starts you just after it, and the count is the distance to the next dash. This pattern handles many real-world codes.

Test your understanding of MID.

You learned to extract characters from anywhere inside a string.

• MID(text, start, count) begins at a position and grabs a number of characters
• Positions start at 1 and include every symbol and space
• Use a large count to reach the end safely
• Pair MID with FIND or SEARCH when the start position varies

Next you will measure how long a piece of text is with LEN.