Master Text Extraction with LEFT, RIGHT & MID (Split Hyphens, Spaces, and Codes in 1 Minute)
Product codes, file extensions, email domains… With LEFT/RIGHT/MID, you can accurately extract only the part you need. We’ll also cover the TEXTBEFORE/TEXTAFTER alternatives in the latest Microsoft 365.
Basic Syntax & Arguments
| Function | Syntax | Description |
|---|---|---|
| LEFT | =LEFT(text, [num_chars]) | Returns the specified number of characters from the left |
| RIGHT | =RIGHT(text, [num_chars]) | Returns the specified number of characters from the right |
| MID | =MID(text, start_num, num_chars) | Returns a specified number of characters starting at a given position |
TIP start_num begins at 1, not 0.
Fixed-Length Extraction (Prefixes/Suffixes)
Example 1) Product code prefix (first 3 characters)
=LEFT(A2,3)Example 2) Last 4 digits of a serial number
=RIGHT(A2,4)Example 3) 5 characters starting with the 4th character
=MID(A2,4,5)Extract by Delimiter (Hyphens, Spaces, and Periods)
Example 4) Product category before a hyphen
=LEFT(A2, FIND("-",A2)-1)Example 5) Detail code after a hyphen
=RIGHT(A2, LEN(A2)-FIND("-",A2))Example 6) Last name after a space (for example, “Kim Minsoo” → “Minsoo”)
=RIGHT(A2, LEN(A2)-FIND(" ",A2))Example 7) Email domain
=RIGHT(A2, LEN(A2)-FIND("@",A2))- FIND is case-sensitive, while SEARCH is not.
- In Microsoft 365,
TEXTBEFORE/AFTERis simpler:=TEXTAFTER(A2,"-")
Working with the Last Delimiter (File Extensions and Names)
Sometimes you need to work from the end, such as when extracting a file extension after the final period (.) or a word after the final space.
Example 8) File extension (after the last period)
=RIGHT(A2, LEN(A2) - FIND("|", SUBSTITUTE(A2, ".", "|", LEN(A2)-LEN(SUBSTITUTE(A2,".","")))))Example 9) Word after the last space
=RIGHT(A2, LEN(A2) - FIND("|", SUBSTITUTE(A2, " ", "|", LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))For Microsoft 365 users, it’s even easier:
=TEXTAFTER(A2, ".", -1) // file extension
=TEXTAFTER(A2, " ", -1) // last wordVariable-Length Codes & the Nth Word
Example 10) Extract text between hyphens (middle code)
=MID(A2, FIND("-",A2)+1, FIND("-",A2, FIND("-",A2)+1) - FIND("-",A2) - 1)Example 11) Nth word (Microsoft 365)
=TEXTSPLIT(A2," ")(1,3) // third wordExample 12) Nth word (traditional method)
=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)), (N-1)*99+1, 99))Cleanup & Error Handling (TRIM/CLEAN/IFERROR)
=IFERROR( TRIM(SUBSTITUTE(MID(A2,4,5),CHAR(160)," ")), "" )TRIM: Removes extra spacesCLEAN: Removes nonprinting charactersSUBSTITUTE: Replaces nonbreaking spaces (CHAR(160)) with regular spacesIFERROR: Handles exceptions such as a missing delimiter with a blank value or message
Common Mistakes and Solutions
- Assuming indexes start at 0 →
MID(text,1,1)returns the first character. - Mixed Korean and English text length → In general,
LENandMIDare sufficient. UseLENB/LEFTB/MIDB/RIGHTBonly when you need byte-based counts. - Repeated delimiters → Split with
TEXTSPLIT, then useINDEXto select the part you need. - Codes that look like numbers → Store them as text to preserve leading zeros (use a leading apostrophe ‘ or apply the Text cell format).
Summary
| Goal | Typical Formula |
|---|---|
| Prefix/suffix | LEFT(A2,n), RIGHT(A2,n) |
| Before/after a delimiter | LEFT(A2,FIND("-",A2)-1), RIGHT(A2,LEN(A2)-FIND("-",A2)) |
| After the last delimiter | TEXTAFTER(A2,"-", -1) or a SUBSTITUTE-based formula |
| Middle text (between two delimiters) | Two nested MID + FIND calls |
FAQ
Should I use FIND or SEARCH?
FIND is case-sensitive, while SEARCH is not. They work almost identically with Korean text; choose FIND only when you need case sensitivity for English text.
I need to perform calculations on numbers after splitting text.
If you extracted only numbers from a text string, convert them with VALUE(), or calculate using numbers in the original data and format the output as text.