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

Get FREE Advanced Excel Exercises with Solutions!

In this article, I will show you the six effective 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.
Furthermore, for conducting the session, I will use the Microsoft 365 version.


How to Remove Last Character in Excel: 6 Ways

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

Dataset to Remove Last Character in Excel


Method-1: Merge VALUE, LEN & REPLACE Functions to Remove 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 for Removing Last Character Only

Steps:

  • Firstly, select the output cell E5.
  • Next, type the following function:
=VALUE(REPLACE(B5,LEN(B5),1,""))

Here, 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.

How to Remove Last Character Using Excel Functions

  • Then, press ENTER, and you will get the output.

  • After that, drag down the Fill Handle icon to paste the used formula to the other cells of the column.

In this way, the following result will appear.

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

Read More: How to Remove First Character in Excel


Method-2: Use of LEFT & LEN Functions to Separate 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 LEFT and LEN functions.

How to Remove Last Character in Excel

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.

Remove Last Character in Excel Using LEFT Function

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


Method-3: Applying MID Function to Delete Some Last Characters 

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.

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.

Using MID Function to Remove Last Character in Excel

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

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


Method-4: Using Flash Fill Feature to Remove Last Character in Excel

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.

Steps:

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

Using Flash Fill to Remove Last Character

  • In cell E6 start typing as the previous one and then like the following the Department names will be suggested.

  • Press ENTER and the following outputs will appear.

Read More: How to Remove First 3 Characters in Excel


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

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 remove these special characters at the first and last place simultaneously using the MID Function.

Removing First and Last Characters Simultaneously in Excel

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 and then the following results will appear.


Method-6: Employing VBA Code to Pull Out 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 Developer Tab >> Visual Basic Option.

Using VBA Code to Remove Last Character in Excel

  • Visual Basic Editor will open.
  • Select Insert Tab >>Module Option.

  • Write 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 here.
=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.

Read More: How to Remove Last Character from String Using VBA in Excel


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 Section to Remove Last Character in Excel


Download Practice Workbook


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.


Related Articles

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo