# How to Remove Last Character in Excel (Easiest 6 Ways)

Consider a dataset with four columns: Student ID, Name, Course No, and Email ID. We will show you how to remove the last characters and extracting the resulting data.

### Method 1 – Merge VALUE, LEN, and REPLACE Functions to Remove the Last Character Only

Suppose that the Student ID consists of 5 characters, where the first 4 are for the year and the last one is enrollment number. To extract the year from this Student ID you have to remove the last character. The extracted values will be shown in the Year Column.

Steps:

• Select the output cell E5.
• Insert the following function:
`=VALUE(REPLACE(B5,LEN(B5),1,""))`

B5 is the student ID. So, LEN(B5) will return the number of the total characters in the B5 cell, and in this case, it is 5 thus 5 will be start_num, 1 is num_chars and the new text is Blank. The VALUE function will convert the string to a number.

• Press Enter, and you will get the output.

• Drag down the Fill Handle icon to paste the used formula to the other cells of the column.

• The following result will appear.

Note
Using the REPLACE function can only remove one character at a time.

Read More: How to Remove First Character in Excel

### Method 2 – Use the LEFT and LEN Functions to Separate the Last Character

In the Course No column, different course names have been created with the Department Name and number. To extract the Department from this Course No, you have to remove the last three digits using the LEFT and LEN functions.

Steps:

• Select the output cell E5.
• Use the following formula:
`=LEFT(D5,LEN(D5)-3)`

Here, D5 is text and LEN(D5)-3 = 5-3=2 is num_chars. So, the first two characters will appear as an output.

• Press Enter to get the output.

• Drag down the Fill Handle icon and you will get the following result.

### Method 3 – Applying the MID Function to Delete Some Last Characters

In the Course No column different course names have been created with the Department Name and number. We’ll remove the last three characters.

Steps:

• Select the output cell E5.
• Use the following formula:
`=MID(D5,1,LEN(D5)-3)`

Here, D5 is text, 1 is the start num, and LEN(D5)-3 is num_char.

• Press Enter to get the output.

• Drag down the Fill Handle icon and you will get the output in the Department column.

### Method 4 – Using the Flash Fill Feature to Remove the Last Character in Excel

Steps:

• Select the output cell E5.
• Type the Department Name according to cell D5.

• In cell E6, start typing, and the Department names will be suggested.

• Press Enter and Excel will AutoFill based on the suggested pattern.

### Method 5-  Combine MID and LEN Functions to Remove First and Last Characters

Suppose in the Email ID column we have some Email IDs, but they are combined with some special characters at the start and end of these IDs. We want to remove these special characters at the first and last place simultaneously using the MID Function.

Steps:

• Select the output cell E5.
• Use the following formula:
`=MID(D5,3,LEN(D5)-4)`
• Press Enter and you will get the output.

Formula Breakdown

Here, D5 is text, 3 is the start num, and LEN(D5)-4 is num_char.
3 is used as the start num because there are 2 special characters before Email Id.
and 4 is subtracted from the total character length in num_char  because there is a total of 4 special characters which you want to omit.

• Drag down the Fill Handle icon.

### Method 6 –   Using VBA Code to Fetch the Last Character in Excel

You can use the VBA code also to remove the last character like in Method-2 or Method-3.

Steps:

•  Select the Developer tab and choose the Visual Basic option.

• Visual Basic Editor will open.
• Select the Insert tab and go to the Module option.

• Use the following code in Module 1.
``````Public Function Remove_Lst_Ch(my_txt As String, my_char_num As Long)
Remove_Lst_Ch = Left(my_txt, Len(my_txt) - my_char_num)
End Function``````

This Code will create a function named Remove_Lst_Ch.

• Save the code and close the window.
• Type the function name in the cell you need the result for.
`=Remove_Lst_Ch(D5,3)`
•  Press Enter and you will get the output.

• Drag down the Fill Handle icon.

After that, the following results will appear.

## Practice Section

We have provided a Practice section like below for each method in each sheet on the right side.

## Related Articles

<< Go Back To Excel Remove Characters from Right | Excel Remove Characters | Data Cleaning in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF