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. Show
Download Workbook6 Ways to Remove Last Character in ExcelHere, 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. Method-1: Using REPLACE Function for Removing Last Character OnlySuppose 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. Step-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. Step-2: Result 📓 Note Read more: How to Remove the Last 3 Characters in Excel Method-2: Using LEFT FunctionIn 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. Step-1: Here, D5 is text and LEN(D5)-3 = 5-3=2 is num_chars. So the first two characters will appear as an output. Step-2: Result Read more: How to Remove Characters in Excel Method-3: Using MID FunctionIn 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. Step-1: Here, D5 is text, 1 is the start num, LEN(D5)-3 is num_char Step-2: Result Read more: Excel Remove Characters From Right Method-4: Using Flash Fill to Remove Last CharacterIn 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. Step-1: Step-2: Step-3: Read more: How to Remove Characters from Left in Excel Method-5: Removing First and Last Characters SimultaneouslySuppose 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. Step-1: Here, D5 is text, 3 is the start num,
LEN(D5)-4 is num_char Step-2: Result Read more: How to Remove Special Characters in Excel Method-6: Using VBA CodeYou can use VBA code also to remove the last character like in Method-2 or Method-3. Step-1: Step-2: Step-3: Step-4:
This Code will create a function named RmvLstCh Step-5: Step-6: Result Practice SectionFor 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. ConclusionIn 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
How do I remove the last character from a value in Excel?6 Ways to Remove Last Character in Excel. Method-1: Using REPLACE Function for Removing Last Character Only.. Method-2: Using LEFT Function.. Method-3: Using MID Function.. Method-4: Using Flash Fill to Remove Last Character.. Method-5: Removing First and Last Characters Simultaneously.. Method-6: Using VBA Code.. How do I remove the last 3 characters from a string in Excel?LEN(D5)-3 ▶ calculates the length of the text, “Jason Roy” and then subtracts the result with 3.. D5 ▶ refers to the cell address of the text “Jason Roy”.. =LEFT(D5,LEN(D5)-3) ▶ truncates the last 3 characters i.e. “Roy” from the text “Jason Roy”.. How do I remove a character from the left of a string in Excel?Remove characters from left side of a cell. =REPLACE(old_text, start_num, num_chars, new_text). =RIGHT(text,[num_chars]). =LEN(text). |