LEFT, RIGHT and MID in Excel: pull part of a text cell
LEFT takes characters from the start of a cell, RIGHT from the end, and MID from the middle. =LEFT(A2,3) returns "Dan". To split names, combine them with FIND to locate the space.
Column G holds each rep's contact name, typed messily on purpose: " dana ruiz", "LUIS ortega ", "priya shah". That's what exported lists look like.
Syntax
=LEFT(text, [num_chars])
=RIGHT(text, [num_chars])
=MID(text, start_num, num_chars)Examples
| Formula | Result |
|---|---|
=LEFT(A2,3) | Dan |
=RIGHT(A4,3) | iya |
=MID(A5,2,3) | arc |
Split a first name from a full name
FIND returns the position of the first space. Take everything before it:
=LEFT(TRIM(G2),FIND(" ",TRIM(G2))-1)TRIM comes first so extra spaces don't throw off the position. Wrap it in PROPER to fix the capitals: =PROPER(LEFT(TRIM(G2),FIND(" ",TRIM(G2))-1)) returns Dana.
Split the last name
Take everything after the space with MID: =PROPER(MID(TRIM(G2),FIND(" ",TRIM(G2))+1,50)) returns Ruiz. The 50 just means "the rest."
FIND vs SEARCH
FIND is case sensitive. SEARCH isn't, and it accepts wildcards. Both return #VALUE! when the text isn't there.
Faster options
In Excel, Data > Text to Columns splits a column at spaces in one step. Microsoft 365 also has TEXTBEFORE and TEXTAFTER, like =TEXTBEFORE(TRIM(G2)," "). Use formulas when the data will keep changing.
Try it: Pull out the first name
In C2, pull Dana's first name out of her contact in G2. Remove the extra spaces first.
Graded live, like a real Excel test. Press New numbers to prove you didn't memorize the answer. More practice
Questions people ask
Does LEFT work on numbers?
Yes, but the result is text. =LEFT(12345,2) returns "12" as text. Wrap it in VALUE() to turn it back into a number.
How do I get the last 4 digits of a number?
Use =RIGHT(A2,4).
Practice on a real spreadsheet
CellReady grades your formula the moment you press Enter and tells you why a wrong answer is wrong. The first 7 lessons are free, with no sign-up to start.
Related guides
Fix extra spaces and random capitals in Excel with TRIM, PROPER, UPPER and LOWER, plus the cases they miss and a live exercise.
How LEN counts characters in Excel, why spaces count, how to find hidden extra spaces, check ID lengths, and a live exercise.
How to combine text in Excel with the & operator, CONCAT and CONCATENATE, how to add spaces and dashes, and a live exercise building a code.