How to remove last character in Excel

July 9, 2022July 9, 2022

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:

• REPLACE and LEN functions
• LEFT and LEN functions
• MID and LEN functions
• FLASH FILL feature
• VBA code

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.

Remove the last character in Excel using REPLACE and LEN functions

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)

where:

• old_text  is the text or numeric value to change  
• start_num is the starting position of the character in old_text that we need to replace with new_text
• num_chars is the number of characters to remove
• new_text is the text that will be replacing the characters in the old_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, "")

where:

• B3 is the cell value from column B that we want to change
• LEN(B3) is the number of characters of the text string from cell B3
• 1 is the num_chars argument which tells Excel how many characters to remove from the text string
• ““ is the empty string that will replace the last character from the text in cell B3

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.

Remove last two characters in Excel using the LEFT and LEN functions

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:

=LEFT(text, LEN(text)-num_of_char)

where:

• text is the text string to modify
• LEN(text) counts how many characters are in the string
• num_of_char is the number of characters to subtract from the right side

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:

=LEFT(B3, LEN(B3)-2) 

where:

• B3 is the cell that contains the string
• LEN(B3) counts the length of the text string in cell B3
• 2 represents the number of characters to remove from the right

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.

Remove the last character in Excel using the MID and LEN functions

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)

where:

• text is the text string to modify
• start_num is the starting position of the character in the text string to modify
• LEN(text) counts the length of the text string to modify
• num_of_char is the number of characters to subtract from the right

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) 

where:

• B3 is the cell that contains the string
• 1 is the start_num
• LEN(B3) is the number of characters in the text string in cell B3
• 3 is the number that tells Excel how many characters from the string to remove

3. Press ENTER to finish the formula. The result shown in the cell will be Chris McCully.

Remove the last character in Excel using the FLASH FILL feature

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.

• Unlike cells with formulas, the results will not update automatically if the original data is changed.
• If it fails to identify the pattern, it will result in an error.
• Sometimes, it incorrectly detects the pattern and produces wrong results.
• The data must be arranged vertically as Flash Fill does not work horizontally.

Tip: You can also use this method to remove the first character from strings.

How to remove characters from right in Excel using VBA code

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 ALT + F11 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 ALT + F8. 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.

What to do next?

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:

• How to separate First and Last names in Excel
• How to add zero in front of a number
• How to remove password from Excel file