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

In this article, I will show you the effective six ways to remove last character in Excel. Sometimes it is needed to extract different texts from a cell by removing the last character. It can be done by typing manually but it is not effective. So, let’s dive into this article and get to know the ways to remove the last character in Excel according to your needs.

Download Workbook

6 Ways to Remove Last Character in Excel

Here, I have a dataset where I am showing four columns; Student Id, Name, Course No, Email Id. Using this data I will try to show you the ways of removing the last character and extracting necessary data.

data set

Method-1: Using REPLACE Function for Removing Last Character Only

Suppose Student Id consists of 5 characters among them first 4 are for a year and the last one is roll number as per this example. Thus, to extract the year from this Student Id you have to remove the last character using the REPLACE Function. The extracted values will be shown in the Year Column.

using replace function

Step-1:
Select the output Cell E5.
➤Type the following function

=VALUE(REPLACE(B5,LEN(B5),1,""))

Here, B5 is the old text, LEN(B5) will return the length of the text and in this case, it is 5 thus 5 will be start_num, 1 is num_chars and new text is Blank.
The Value Function will convert the string to a number.

Replace function

Step-2:
➤Press ENTER and you will get the output.
➤Drag down the Fill Handle

replace function

Result
In this way, the following result will appear.

remove last character

📓 Note
Using REPLACE Function you will not be able to remove more than one character from last.

Read more: How to Remove the Last 3 Characters in Excel

Method-2: Using LEFT Function

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 Function.

Left Function

Step-1:
➤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.

Left function

Step-2:
➤Press ENTER and you will get the output.
➤Drag down the Fill Handle

Left function

Result
Then you will get the following result.

remove last character in Excel

Read more: How to Remove Characters in Excel

Method-3: Using MID Function

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 MID Function.

MID function

Step-1:
➤Select the output Cell E5.
➤Use the following formula

=MID(D5,1,LEN(D5)-3)

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

MID function

Step-2:
➤Press ENTER and you will get the output.
➤Drag down the Fill Handle

MID Function

Result
Now you will get the output in the Department column.

remove last character in Excel

Read more: Excel Remove Characters From Right

Method-4: Using Flash Fill to Remove 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 Flash Fill feature.

Flash Fill feature

Step-1:
➤Select the output Cell E5.
➤Type the Department Name according to Cell D5.

Flash fill feature

Step-2:
➤In Cell E6 start typing as the previous one and then as following the Department names will be suggested.

Flash fill feature

Step-3:
➤Press ENTER and the following outputs will appear.

removing last character

Read more: How to Remove Characters from Left in Excel

Method-5: Removing First and Last Characters Simultaneously

Suppose in the Email Id column I have some Email Ids but they are combined with some special characters at the start and end of these Ids. Now I want to omit these signs at the first and last place simultaneously using the MID Function.

using MID Function

Step-1:
➤Select the output Cell E5.
➤Use the following formula

=MID(D5,3,LEN(D5)-4)

Here, D5 is text, 3 is the start num, LEN(D5)-4 is num_char
3 is used as 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 total of 4 special characters which you want to omit.

MID Function

Step-2:
➤Press ENTER and you will get the output.
➤Drag down the Fill Handle

MID Function

Result
Now the following results will appear.

remove last character

Read more: How to Remove Special Characters in Excel

Method-6: Using VBA Code

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

Step-1:
➤Select Developer Tab >>Visual Basic Option

VBA developer

Step-2:
Visual Basic Editor will open
➤Select Insert Tab >>Module Option

Insert Module

Step-3:
Module 1 will be created.

module in VBA Developer

Step-4:
➤Write the following code here.

Public Function RmvLstCh(txt As String, char_no As Long)

RmvLstCh = Left(txt, Len(txt) - char_no)

End Function

This Code will create a function named RmvLstCh
Save the code and Close the window.

VBA Code

Step-5:
➤Select the output Cell E5
➤Type the function name here.

=RmvLstCh(D5,3)

VBA Code

Step-6:
➤Press ENTER and you will get the output.
➤Drag down the Fill Handle

VBA code

Result
After that, the following results will appear.

remove last character

Practice Section

For doing practice by yourself we have provided a Practice section like below for each method in each sheet on the right side. Please do it by yourself.

Practice

Conclusion

In this article, I tried to cover the easiest ways to remove the last character in Excel effectively. Hope you will find it useful. If you have any suggestions or questions feel free to share them with us.


Further Readings

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo