Extracting Text With LEFT and RIGHT

Spreadsheets are full of text that needs to be pulled apart. Maybe you have product codes where the first two letters mean a category, or phone numbers where you only want the area code.

Instead of editing every cell by hand, you can use text functions to grab exactly the characters you need. The two simplest are LEFT and RIGHT. They pull characters from the start or the end of a piece of text.

The LEFT function returns characters from the beginning of a text string. It takes two pieces of information:

• The text (or a cell holding text)
• How many characters to grab

So LEFT("Spreadsheet", 6) returns Spread. It starts at the far left and counts six characters to the right.

In real work you point LEFT at a cell instead of typing text. Imagine cell A2 holds the order code US-4471.

To pull the country prefix, count the first two characters:

The formula reads cell A2, grabs the first 2 characters, and returns US. Change A2 and the result updates automatically.

RIGHT works exactly like LEFT but counts from the end of the text. It also takes the text and a count.

RIGHT("Spreadsheet", 5) returns sheet — the last five characters.

Think of it as standing at the right edge of the word and counting backwards.

Using the same order code US-4471 in cell A2, suppose you want just the number on the end.

The four digits sit at the right side, so ask for the last 4 characters. This returns 4471. If the codes always end in a four-digit number, this formula works for every row.

The character count is optional. If you leave it out, both functions return a single character.

• LEFT("Hello") returns H
• RIGHT("Hello") returns o

This is handy when you only need the very first or very last letter, such as grabbing an initial from a name.

Say cell A2 holds a first name like Amara and B2 holds a last name like Okafor. You want the initials AO.

Grab the first letter of each and join them with the & symbol. LEFT(A2,1) gives A, LEFT(B2,1) gives O, and the ampersand sticks them together.

What if you ask for more characters than the text actually has? There is no error — the function simply returns the whole string.

LEFT("Hi", 10) returns Hi because that is all there is. This makes LEFT and RIGHT forgiving when your text lengths vary from row to row.

When you use LEFT or RIGHT on a number, the spreadsheet first converts it to text, then extracts characters.

If A2 holds 2026, then LEFT(A2, 2) returns the text 20. Note the result is text, not a number, so it will line up on the left in a cell. To do math with it, wrap it in VALUE.

You can use both in the same sheet to split a code into parts. With US-4471 in A2:

• Prefix: LEFT(A2, 2) gives US
• Number: RIGHT(A2, 4) gives 4471

Put each formula in its own column and you have cleanly separated data ready for sorting or filtering.

Good news: LEFT and RIGHT behave the same way in Microsoft Excel and Google Sheets. The syntax is identical, so a formula written in one works in the other.

This consistency is true for most basic text functions, which makes them safe to learn once and use everywhere.

Test your understanding of LEFT and RIGHT.

You learned to pull characters from either edge of a text string.

• LEFT(text, n) grabs the first n characters
• RIGHT(text, n) grabs the last n characters
• Leaving out n returns a single character
• Asking for too many returns the whole string
• Join results with &, and wrap in VALUE to get a number back

Next you will reach into the middle of a string with MID.