Sometimes, the data in your spreadsheet may not be formatted as you need it to be. Removing extra characters can be tedious, and doing it manually is time-consuming and prone to error. Luckily, there are easy ways to manipulate data using Excel functions and formulas. In this article, you will learn how to remove last character in Excel using:
Before we begin, I encourage you to download the exercise file. It provides all the formulas I've used in this tutorial and can prove helpful in understanding how each formula works.
REPLACE function replaces a part of a string within the cell with a new string you provide. To remove the last character in the text string, we can change it to an empty value. The syntax for this formula is:
=REPLACE(old_text, start_num, num_chars, new_text)
Since each name has a variable length, instead of specifying the start_num, we will use the LEN function as an argument for the REPLACE function. The Excel LEN function returns the total length of a given text as the number of characters. We can then use the LEN formula as the start_num argument, which will return the total length of the text string, including any spaces and punctuation. By combining the two functions, we get the below formula:
=REPLACE(old_text, LEN(text), num_chars, new_text)
In the sample below, we want to remove the middle name initial from each cell from column B. To achieve this, we will use the REPLACE and LEN functions to replace the middle name initials with an empty string.
To write the formula, follow these steps:
1. Click on the cell where you want to see your desired value.
2. Enter this formula in the Excel formula bar:
=REPLACE(B3, LEN(B3), 1, "")
3. Press ENTER, and you will get the string without the last character: Chris McCully
4. To apply the formula to the below cells, drag down the Fill Handle.
Note: This formula can be used to remove the first or last character in Excel. Adjust it to look at the first character instead of the last one, and you should be ready to go. You can do this using 1 instead of LEN(B3), and the REPLACE function will change the first character.
If you need to remove more than one character in Excel, you can use the LEFT function instead. Using the LEFT function, we will identify the text strings to modify, count the characters of the text string that will be changed, and subtract the number of characters to remove from the right side. The formula is:
For example, if we want to remove the middle name initial with a period on each name in column B, we need to remove the last two characters. The following formula will help us achieve this.
1. Click on the cell where you want to output the result of the formula.
2. Enter the following formula:
Note: If you need to remove more characters from the right, all you need to do is replace the number from the above formula.
3. Press ENTER to finish the formula, and you will get the name omitting the middle name initial.
Note: Remember that the LEN function will count characters regardless of whether they are spaces or punctuations, so the space character between McCully and P. will not be removed.
4. Drag the Fill Handle to apply the formula to other cells as needed.
Tip: You can use the RIGHT function if you need to remove the first letter or character in a text string. Type RIGHT instead of LEFT in the above formula, and it will remove the characters from the left side of the text string.
Aside from the LEFT function, the MID function is another way to remove characters in Excel. It works the same way as the LEFT function, and the formula is almost the same, except that it has another argument which is start_num. Using the MID function also allows you to remove characters from the middle of the string, rather than just at the beginning or the end. This method is very helpful when manipulating text strings in Excel. The generic formula looks like this:
=MID(text, start_num, LEN(text)-num_of_char)
In our previous example with the LEFT function, the space between the first name and middle initial was retained since we just removed two characters from the right. If we also want to remove the space, we need to remove three characters from the right.
1. Click on a cell where you want to insert your formula.
2. In the Excel formula bar, write:
=MID(B3, 1, LEN(B3)-3)
3. Press ENTER to finish the formula. The result shown in the cell will be Chris McCully.
Flash Fill is a feature in Microsoft Excel that is only available in Excel 2013 and later versions. If Microsoft Excel detects a pattern when entering data, this tool will automatically display data in the below cells based on the pattern you entered. If you start typing surnames without the first names, Excel will identify the pattern and suggest data with the same structure.
Here is an excellent YouTube tutorial on how to use Flash Fill to manipulate names:
If the data preview doesn’t show automatically, you can follow the instructions below.
For example, if we want to extract the last name only in all the cells from column B, we can use Flash Fill by clicking the Fill Handle button.
1. Type the value you want to extract on the cell adjacent to the cell where the original value is stored. In our example, insert the last name in cell C3.
2. Navigate to the second cell and start typing the last name. The Flash Fill suggestion menu will appear.
3. Press ENTER to accept the suggestion. Excel will fill all the empty cells with the data corresponding to the identified pattern.
Alternatively, after step 1, you can press the Flash Fill button from the Data tab. The result is the same.
Although Flash Fill makes work easier and saves a lot of time, there are some limitations you should keep in mind while using this feature.
Tip: You can also use this method to remove the first character from strings.
If you know your way around VBA, then you can also write your own code to remove characters from right in Excel. Alternatively, if you want to use VBA but don't know how to write the code from scratch, you can use the following steps and insert the below code in a VBA module.
1. Press+ to open the Microsoft Visual Basic editor.
2. Click insert module or double click on the sheet where your text is located.
3. Copy and paste the following code below in the script editor.
Sub Remove_Last_Characters() On Error GoTo ErrorMessage Dim n As Integer n = Int(InputBox("How many last characters to remove? ")) For i = 1 To Selection.Rows.Count For j = 1 To Selection.Columns.Count Selection.Cells(i, j) = Left(Selection.Cells(i, j), Len(Selection.Cells(i, j)) - n) Next j Next i Exit Sub ErrorMessage: MsgBox "Please insert an integer number that is less than or equal to the length of the string." End Sub
4. Save the VBA code and close the Microsoft Visual Basic editor.
5. Select your data and press+ . The macro window will appear.
6. Select the Remove_Last_Characters Macro and click Run.
7. Enter the number of characters you want to remove and click OK. The VBA code will remove N characters from the right from all the selected cells. Remember that the numeric value you use needs to be an integer.
An optional method for more advanced Excel users would be to create a user-defined function using VBA and access it from the formula bar. Leave a comment below if you want to see this method explained in the article.
There you have it. These are five easy ways to remove the last characters in Excel. If you have any questions, write a comment, and I'll help you as soon as possible.
If you are looking to improve your Excel skills, then I strongly suggest reading the following articles:
My name is Radu Meghes, and I'm the owner of excelexplained.com. Over the past 15+ years, I have been using Microsoft Excel in my day-to-day job. I’ve worked as an investment and business analyst, and Excel has always been my most powerful weapon. Its flexibility and complexity make it a highly demanded skill for finance employees. I launched excelexplained.com back in 2017, and it has become a trusted source for Excel tutorials for hundreds of thousands of people each year.
If you'd like to get in touch, you can contact me on LinkedIn.
Thousands of people have benefited from my free Excel lessons. My only question is, will you be next?
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.