Remove the First or Last Characters in Excel

To remove the first character in Excel, you don't delete anything. You keep everything except that character with =RIGHT(A2,LEN(A2)-1). The same idea, turned around, removes the last character: =LEFT(A2,LEN(A2)-1). This guide shows both, how to remove any number of characters, and how to avoid the errors that come from empty cells.

How do you remove the first character in Excel?#

Combine RIGHT and LEN: LEN counts the characters, and RIGHT keeps all of them except one. Here is the formula:

=RIGHT(A2,LEN(A2)-1)

Read it from the inside out:

The result is 10432. Because the formula works out the length for each cell, it works on text of any length. Fill it down (Windows: Ctrl+D; Mac: ⌘D) and every row loses exactly one character from the start.

If you are new to these functions, the guide to LEFT, RIGHT and MID for pulling out part of a cell explains each one with simple examples.

Two other ways to drop the first character#

REPLACE and MID give the same result, and some people find them easier to read. All three formulas below turn X10432 into 10432:

REPLACE is the shortest: start at position 1, replace 1 character, put in "" (empty text). MID asks for more characters than are left, which is fine. MID simply stops at the end of the text.

Pick one and use it consistently. In a job test, RIGHT with LEN is the version examiners most often expect, because it shows you understand how LEN feeds the length.

How to remove the last character#

Swap RIGHT for LEFT to keep everything except the last character:

=LEFT(A2,LEN(A2)-1)

This is useful for trailing punctuation or unit letters. To drop a two-letter unit such as kg, remove 2 characters instead of 1. Here are some common cases:

The last row removes the first and the last character at once. MID starts at character 2 and takes the total length minus 2, which leaves out both ends.

How to remove the first or last N characters#

Change the 1 in the formula to however many characters you want to remove. To remove the first 3 characters of US-40512:

=RIGHT(A2,LEN(A2)-3)

The result is 40512. To make the number easy to change, put it in a cell, say E1, and lock it with dollar signs:

=RIGHT(A2,LEN(A2)-$E$1)

Now one edit in E1 updates every row. This works well when a data export adds a fixed prefix, such as a country code, that you need to strip.

Remove the first character only if it is a certain character#

Sometimes only some rows have the unwanted character. A list might mix X10432 with 20519, and you want to strip the X only where it exists. Check the first character with LEFT inside IF:

=IF(LEFT(A2,1)="X",RIGHT(A2,LEN(A2)-1),A2)

If the first character is X, the formula removes it. If not, it returns the cell unchanged. The check is not case-sensitive, so a lowercase x is removed too. This is safer than removing one character from every row and losing a real digit by mistake.

When the part you want to remove is not a fixed length, such as everything before a dash, counting won't work. Use a delimiter instead: the comparison of LEFT and TEXTBEFORE for text before a character shows when each is better. The guide to removing unwanted characters with SUBSTITUTE covers characters that can appear anywhere in the cell.

Why does the formula return #VALUE! or the wrong result?#

Most problems come from cells that are shorter than you think, or longer. Check these:

1. Empty cells. If A2 is blank, LEN(A2)-1 is -1. RIGHT and LEFT can't take a negative length, so Excel shows #VALUE!. Guard against it:

   Excel formula

   =IF(A2="","",RIGHT(A2,LEN(A2)-1))

2. Hidden spaces. A leading space counts as a character, so the formula removes the space instead of the letter. Clean the text first: =RIGHT(TRIM(A2),LEN(TRIM(A2))-1). To check the length of a cell, the guide on counting characters with LEN helps.

3. Numbers that won't add up. RIGHT, LEFT, MID and REPLACE always return text. 10432 sits on the left of the cell and SUM ignores it. Wrap the formula in VALUE to get a real number: =VALUE(RIGHT(A2,LEN(A2)-1)).

4. Numbers with formatting. If A2 is a number shown as $1,250.00, LEN counts the stored value 1250, not what you see. Text functions work on the stored value.

Can you remove characters without a formula?#

Yes. Flash Fill copies a pattern you type. Type 10432 next to X10432, select the cell below, and press Ctrl+E on Windows (or use Data > Flash Fill, which works on both Windows and Mac). Excel fills the rest of the column.

Flash Fill gives you fixed values, not formulas, so the results don't update if the source changes. It also guesses the rule from your examples, so scan the results before you trust them. For data that changes, or for a test that asks for a formula, use RIGHT and LEN.

Practise it#

Removing characters is a small skill that shows up in data-cleaning tasks and job tests all the time. Practise LEFT, RIGHT, MID and LEN in a live spreadsheet: you type the formula, and it checks your answer instantly. For quick syntax reminders, keep the RIGHT function guide, the LEFT function guide and the LEN function guide open while you work, or try the exercise on this page first.