How to remove the First and/or Last Character from a Cell in Excel

Tags Excel

This guide explains how to remove the first and/or last character from a cell in Excel using formulas. You can use the REPLACE, RIGHT & LEN, or LEFT & LEN functions to modify text values efficiently.

Prerequisites

  • A basic understanding of Excel formulas.
  • A dataset where you need to remove the first and/or last character from cell values.

Steps

1. Remove the First Character in Excel

  • To delete the first character from a string, you can use one of the following formulas:

Using the REPLACE Function: 

=REPLACE(A2, 1, 1, "")

  • This formula takes the first character from A2 and replaces it with an empty string ("").

Using the RIGHT and LEN Functions: 

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

  • This formula calculates the total length of the string using LEN(A2), subtracts one character, and extracts the remaining characters from the right.

Example:
If cell A2 contains "Hello", the result will be "ello".

2. Remove Multiple Characters from the Left

  • If you need to remove more than one character from the beginning, modify the formulas by specifying the number of characters to delete:

Removing First 2 Characters:

=REPLACE(A2, 1, 2, "")

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

Removing First 3 Characters: 

=REPLACE(A2, 1, 3, "")

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

3. Remove the Last Character in Excel

  • To delete the last character from a string, use either of the following formulas:

Using the LEFT and LEN Functions: 

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

  • This formula extracts all but the last character by subtracting 1 from the total length.

Example:
If A2 contains "Hello", the result will be "Hell".

Image 1.png

image 2.png

Additional Information and Troubleshooting Tips

Common Errors & Fixes:

  • #VALUE! Error: Ensure the cell contains text or numbers and is not empty.
  • Formula Not Working: Double-check that the cell reference (e.g., A2) is correct.

Helpful links

Print Article