Remove Characters from the Start, End, or Anywhere

Excel & Google Sheets

How it works

Three tools cover the three cases. To drop the first N characters, start MID at position N+1 and take a huge length — =MID(A2,3,999) skips 2 characters and 999 simply means "the rest of the text." To drop the last N characters, keep everything except them: =LEFT(A2,LEN(A2)-2) measures the full length and keeps all but the final 2. To delete a specific character wherever it appears, replace it with nothing: =SUBSTITUTE(A2,"-",""). These are safe on every row because they work from positions and lengths, not from what the characters actually are.

MID(A2,3,999)

Remove the first 2 characters: start at the 3rd, take everything after. To remove N, start at N+1.

LEFT(A2,LEN(A2)-2)

Remove the last 2 characters: keep the length minus 2. To remove N, subtract N.

SUBSTITUTE(A2,"-","")

Remove every dash, wherever it sits. Swap in any character or substring.

When to use it

Use it to strip system prefixes off order numbers, drop "-US" style suffixes before matching lists, or clean dashes and spaces out of phone numbers and SKUs before a lookup.

Common mistakes

• ✕Using MID(A2,2,999) to remove 2 characters.

  ✓MID's second argument is where to START, not how many to skip. To remove 2 characters, start at 3: =MID(A2,3,999).

• ✕LEFT(A2,LEN(A2)-2) on cells shorter than 2 characters.

  ✓A negative length returns #VALUE!. Guard short or blank cells: =LEFT(A2,MAX(LEN(A2)-2,0)).

• ✕Expecting SUBSTITUTE to change the original cell.

  ✓Formulas never edit their source. Put the formula in a helper column, then Copy → Paste Special → Values over the original if you want it replaced.