FEBRUARY 5, 2025
How to remove the first or last character in Excel
Removing the first or last character from a text string in Microsoft Excel is a common task in data management.
Whether you’re working with city codes, numeric values, or unwanted characters, Excel provides multiple simple ways to clean and format your data.
Using powerful Excel functions like LEFT function, RIGHT function, LEN function, and REPLACE function, you can easily manipulate text to achieve the desired result.
In this guide, we’ll explore the best ways to remove the first character, last character of a string, or even a fixed number of characters from your data.
We’ll cover simple formulas, the beauty of Flash Fill, and even VBA programming for handling large datasets efficiently.
Whether you’re a beginner or an advanced user, these techniques will help you improve your data management capabilities in Excel or free office suites like WPS Office.
Let’s dive in!
You may also want to see How to add and subtract a date from today in Excel.
Removing the First Character in Excel
To remove the first character of a text string, use the RIGHT function combined with the LEN function:
Generic Formula
=RIGHT(B2, LEN(B2)-1)
Explanation
The LEN function calculates the total length of the string in cell B2.
The RIGHT function extracts the rest of the text (excluding the first character).
Press Enter, and then use the fill handle to apply the formula to the rest of the cells.
Removing the Last Character In Excel
To remove the last character of a string, use the LEFT function:
Below Formula
=LEFT(B2, LEN(B2)-1)
Explanation
The LEFT function extracts all characters except the last character of a cell.
The LEN function ensures the correct fixed number of characters.
This method works well for text values, but if you’re dealing with a numeric value, consider using the VALUE function.
You may also want to see How to delete multiple sheets or tabs in Excel.
Removing First N or Last N Characters
If you need to remove a variable length of characters:
First N Characters
=RIGHT(B2, LEN(B2)-N)
Last N Characters
=LEFT(B2, LEN(B2)-N)
Replace N with the number of specific characters you want to remove.
Using the REPLACE Function
Another simple way to remove characters is by using the REPLACE function:
Remove First Character
=REPLACE(B2,1,1,””)
Remove Last Character
=REPLACE(B2, LEN(B2), 1, “”)
This function replaces the unwanted character with an empty string.
Ideal for text formatting in Microsoft Office applications.
You may also want to see: How to calculate weighted average in Excel with percentages.
Using the SUBSTITUTE Function for Specific Characters
If you want to remove a space character, a last letter, or any additional character, use the SUBSTITUTE function:
=SUBSTITUTE(B2, “X”, “”)
Replace “X” with the character you want to remove.
Using Flash Fill (Beauty of Flash Fill)
For a simple process, enter the modified text manually in an adjacent cell and use the Flash Fill feature:
Type the corrected new string manually.
Press Ctrl + E to apply the pattern to the rest of the cells.
You may also want to see: How to apply the same formula to multiple cells in Excel.
Removing Characters with VBA Code
For large datasets, VBA programming allows batch processing.
Following Code for First Character (vba)
Function RemoveFirstCharacter(rng As Range) As String
RemoveFirstCharacter = Right(rng.Value, Len(rng.Value) – 1)
End Function
Following Code for Last Character (vba)
Function RemoveLastCharacter(rng As Range) As String
RemoveLastCharacter = Left(rng.Value, Len(rng.Value) – 1)
End Function
These custom functions can be applied in a single location.
Great for effective data management in Microsoft Excel or WPS Office.
Using Complex Formulas for Text Manipulation
For handling extra characters and data formats, consider combining functions (excel):
=TRIM(LEFT(B2, LEN(B2)-1))
The TRIM function removes extra spaces in text manipulation.
You may also want to see You may also want to see: How to add specific cells in excel (summing cells).
Conclusion
Cleaning up text data in Microsoft Excel is an essential skill for effective data management, whether you’re working with city names, numeric values, or unwanted characters.
With simple formulas like the LEFT function, RIGHT function, LEN function, and REPLACE function, you can easily remove the first character, last character of a string, or even a fixed number of characters.
For those handling large datasets, the Flash Fill feature, VBA programming, and custom functions provide more automated and scalable solutions.
No matter your skill level, these easy ways help ensure clean, formatted data without extra spaces or additional characters.
You may also want to see How to delete multiple sheets or tabs in Excel.
Important: This post is for informational and educational purposes only. This post should not be taken as therapy advice, financial advice or used as a substitute for such. You should always speak to your own therapist or financial advisor before implementing this information on your own. Thank you!
