Excel MID, LEFT, and RIGHT Functions Guide: Finding Positions and Variable-Length Text

Complete Guide to Excel MID, LEFT, and RIGHT Functions: From Basics to Variable-Length Applications

Excel MID, LEFT, and RIGHT functions are the most basic tools for extracting the specific “part” you need from text. This guide covers practical patterns you can use right away, from basic syntax to variable-length extraction (FIND/SEARCH), Korean text/byte considerations, and working with tables and dynamic arrays.

Quick Fix

Basic Syntax at a Glance

LEFT(text, [num_chars]): Returns num_chars characters from the left.
RIGHT(text, [num_chars]): Returns num_chars characters from the right.
MID(text, start_num, num_chars): Returns num_chars characters starting at character start_num.
Position helpers: FIND() = case-sensitive; SEARCH() = case-insensitive and supports wildcards.
Length: LEN() (number of characters). In older versions or certain locales, LENB() returns the number of bytes.

10 Common Patterns

Last 4 digits of a phone number: =RIGHT(A2,4)
Email username (before @): =LEFT(A2, FIND("@",A2)-1)
Email domain (after @): =MID(A2, FIND("@",A2)+1, LEN(A2))
File extension: =RIGHT(A2, LEN(A2)-FIND("@",SUBSTITUTE(A2,".","@",LEN(A2)-LEN(SUBSTITUTE(A2,".","")))))
First 3 characters: =LEFT(A2,3)
Last 2 characters: =RIGHT(A2,2)
Code before a hyphen: =LEFT(A2, FIND("-",A2)-1)
Code after a hyphen: =MID(A2, FIND("-",A2)+1, LEN(A2))
After the nth delimiter (for example, after the second “-”): =MID(A2, FIND("@",SUBSTITUTE(A2,"-","@",2))+1, LEN(A2))
Extract numbers only/text only (365):
Numbers only: =TEXTJOIN("",,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,""))

Text only: =TEXTJOIN("",,IF(ISTEXT(MID(A2,SEQUENCE(LEN(A2)),1)),MID(A2,SEQUENCE(LEN(A2)),1),""))

Why These Issues Happen (Concepts and Principles)

The Core of Position-Based Extraction

Parsing text ultimately comes down to the starting position and length. If you do not know the length, calculate it with an expression such as LEN(text) - start+1, or use FIND/SEARCH to locate the next boundary and use the difference as the length.

FIND vs. SEARCH, LEN vs. LENB

FIND: Case-sensitive and does not support wildcards. SEARCH: Case-insensitive and supports the ?, * wildcards.
In modern Excel and Unicode environments, use MID/LEFT/RIGHT. MIDB/LEFTB/RIGHTB/LENB are for backward compatibility with older versions that handle DBCS (Chinese, Japanese, and Korean) byte counts, and mixing them can be confusing.

Practical Examples

Sample Data

Row	A (Original Text)	Purpose
2	p1001-SEOUL-XL	Split SKU
3	kim@exceljump.com	Split email address
4	010-1234-5678	Last phone digits
5	Report_v2.1_final.xlsx	File extension
6	[KR] 2025-11-13 / CENTER-02	Symbol-based extraction

1) Split an SKU/Code (Fixed and Variable Lengths Combined)

When A2 is p1001-SEOUL-XL:

Product code (before the hyphen): =LEFT(A2, FIND("-",A2)-1) → p1001
City (between the first and second hyphens): =MID(A2, FIND("-",A2)+1, FIND("@",SUBSTITUTE(A2,"-","@",2)) - FIND("-",A2) - 1) → SEOUL
Size (after the last hyphen): =MID(A2, FIND("@",SUBSTITUTE(A2,"-","@",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))+1, LEN(A2)) → XL

2) Split an Email Address

Username: =LEFT(A3, FIND("@",A3)-1) → kim
Domain: =MID(A3, FIND("@",A3)+1, LEN(A3)) → exceljump.com
Top-level domain (TLD): =RIGHT(A3, LEN(A3)-FIND(".",A3, FIND("@",A3))) → com

3) Phone Number / Last 4 Digits

=RIGHT(SUBSTITUTE(A4,"-",""),4) → 5678
Returns the last four digits whether or not hyphens are present.

4) Extract a File Extension (Based on the Last Period)

=RIGHT(A5, LEN(A5)-FIND("@",SUBSTITUTE(A5,".","@", LEN(A5)-LEN(SUBSTITUTE(A5,".",""))))) → xlsx

5) Extract a Specific Section from a Complex String

A6 = [KR] 2025-11-13 / CENTER-02

Country code (inside the brackets): =MID(A6, FIND("[",A6)+1, FIND("]",A6)-FIND("[",A6)-1) → KR
Center code (after the slash and space): =MID(A6, FIND("/",A6)+2, LEN(A6)) → CENTER-02

6) Tables (Structured References) and Dynamic Arrays

If you convert a range to a table (Ctrl+T), name it tblOrders, and it has an [Email] column:

=LEFT(tblOrders[Email], FIND("@", tblOrders[Email]) - 1)

In Microsoft 365/2021 or later, TEXTSPLIT is even simpler:

=TEXTSPLIT(tblOrders[Email], "@")

The first spilled column contains the usernames, and the second contains the domains.

7) Create More Readable Formulas with LET

=LET(
  s, A2,
  p1, FIND("-", s),
  p2, FIND("@", SUBSTITUTE(s,"-","@",2)),
  code, LEFT(s, p1-1),
  city, MID(s, p1+1, p2-p1-1),
  size, MID(s, p2+1, LEN(s)),
  code & " | " & city & " | " & size
)

Replacing repeated calculations with variables improves both readability and performance.

Alternatives, Notes, and Checklist

For variable-length text, always use FIND/SEARCH to identify the “boundary” position and calculate the length.
For Korean and multilingual text, use MID/LEFT/RIGHT by default. Consider byte functions (such as MIDB) only in specialized DBCS environments.
Remove extra spaces and nonbreaking spaces (CHAR(160)) with TRIM/SUBSTITUTE before parsing.
If you need to perform numeric calculations, convert extracted text to a number with VALUE().
If the same pattern repeats, consider converting the range to a table (structured references) or using Power Query for permanent transformations.

Troubleshooting

Symptom	Cause	Solution
#VALUE! / #NUM!	FIND result is 0 or the length is negative; an error in the starting-position calculation	Check whether the boundary character exists and handle exceptions with `IFERROR`
Email domain is truncated	The last-period position was not calculated	Use `SUBSTITUTE(...,".","@",count)` to find the last period
Korean characters are garbled	Byte functions are being mixed in	Use only `MID/LEFT/RIGHT` (recommended for modern Unicode)
Some values have hyphens and others do not	The text was not normalized	Standardize it first with `SUBSTITUTE(text,"-","")`
Large datasets are slow	Complex nested FIND calculations are repeated	Eliminate repeated calculations with `LET`, or preprocess with Power Query

엑셀 공백 일괄 삽입/제거 완전 가이드: TRIM·SUBSTITUTE·CLEAN·TEXTSPLIT/TEXTJOIN·Power Query

Excel MID, LEFT, and RIGHT Functions Guide: Finding Positions and Variable-Length Text