5 Ways to Remove Last Character from String in Excel

When a string contains different sets of data, we can extract every piece of information to different cells by removing a certain number of characters. Retrieving those data manually is inefficient and cumbersome. In this article, we will introduce 5 convenient ways to remove last character from string in Excel.


📁  Download Excel File

Download the following Excel workbook to realize the topic more clearly.


Learn to Remove Last Character from String in Excel with These 5 Suitable Approaches

Today we will use the following sample dataset to show how to remove last character from string in Excel.


Approach

1. Utilizing Flash Fill Faeature

In our first approach, we will use the Flash Fill feature to eliminate the last character. Perhaps, this is the easiest way to remove the last characters.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, select cell D6 and type in the Year 2019, and press Enter.
  • In cell D7, when you enter 2, you will notice that Excel is suggesting the Years.

how to remove last character from string using flash fill

  • Next, click cell D11 to apply the suggestions.

  • That’s it! We have removed the last characters from the string.

📕 Read More: 3 Ways to Remove Non-Numeric Characters from Cells in Excel


Approach

2. Combining LEFT and LEN Functions

Next, we will utilize the combination of LEFT and LEN functions to extract the Year from the ID.

 ⬇️⬇️ STEPS ⬇️⬇️

  • First, select cell D6 and Enter the formula below:

=LEFT(C6,LEN(C6)-4)

how to remove last character from string applying left and len functions

  • Later, use the Fill Handle icon from the bottom right corner of cell D6 and double-click.

  • Great! We removed the last 4 characters to extract the Year from the ID.

🔨 Formula Breakdown

LEFT(C6,LEN(C6)-4)

👉  The LEN function returns the length of the string stored in cell C6.

👉  The LEFT function will remove the last 4 characters from the text present in cell C6.


Approach

3. Merging MID and LEN Functions

In our third approach, we will merge MID and LEN functions to remove last characters to find the Year.

⬇️⬇️ STEPS ⬇️⬇️

  • In the beginning, select cell D6 and type in the following formula, and press Enter:

=MID(C6,1,LEN(C6)-4)

how to remove last character from string in excel implementing mid and len functions

  • Use the Fill Handle icon just like in the previous approach.

  • Voila! We have removed the last 4 characters from the ID.

🔨 Formula Breakdown

MID(C6,1,LEN(C6)-4)

👉  The LEN function will return the number of characters present in cell C6. We subtracted 4 because we want the last 4 characters to be removed.

👉  The MID function will keep the first 4 characters of cell C6.


Approach

4. Coalescing REPLACE and LEN Functions

In our penultimate approach, we will coalesce REPLACE and LEN functions to strip the very last character from the string. Suppose we imported the ID from a different source and the IDs contain delimiters. We will remove those delimiters here.

⬇️⬇️ STEPS ⬇️⬇️

  • To begin with, select cell D6 and Enter the formula below:

=REPLACE(C6,LEN(C6),1,””)

how to remove last character from string utilizing replace and len functions

  • Use the Fill Handle icon again.

  • Done! Consequently, we have the corrected ID.

🔨 Formula Breakdown

REPLACE(C6,LEN(C6),1,””)

👉  We used cell C6 as the old text for the REPLACE function.

👉  Next, the LEN function returnee will work as the start number from where we want to replace. That will be the last character of the string.

👉  We put 1 as the number of characters since we want to replace only 1 character with an empty string.


Approach

5. Executing VBA Code

In the final approach, we will create a custom function executing a VBA code to remove the last character from the string.

⬇️⬇️ STEPS ⬇️⬇️

  • First, navigate to the Developer tab and click Visual Basic.

  • A Microsoft Visual Basic for Applications window will appear on the screen.
  • From the Insert tab, click on Module.

  • A code window will open momentarily.
  • Copy and Paste the following code in the window and press Ctrl+S to save the code. We have created a function and we will use that now.
Public Function REMOVECHARS(s As String, i  As Integer)
REMOVECHARS = Left(s, Len(s) - i)
End Function

how to remove last character from string executing vba code to create custom function

  • Afterward, move back to the worksheet. Select cell D6 and Enter the following formula:

=REMOVECHARS(C6,4)

how to remove last character from string in excel embedding vba code to create custom function

  • Use the Fill Handle icon just like before.

  • As a result, the last 4 characters are removed.

How to Remove First and Last Character Concurrently in Excel

Now, we will learn how to remove first and last characters concurrently from a string using the MID function. We will extract the Code from the ID removing the Year and the Initials.

⬇️⬇️ STEPS ⬇️⬇️

  • To start with, select cell D6 and Enter the formula below:

=MID(C6,5,LEN(C6)-6)

how to remove first and last characters concurrently in excel using mid and len functions

  • Use the Fill Handle icon to apply the formula to the whole column.

  • Eventually, we have the Code for each employee.

🔨 Formula Breakdown

MID(C6,5,LEN(C6)-6)

👉  The LEN function counts the number of characters in cell C6. We subtracted 6 because we will remove 6 characters in total.

👉  The MID function will return the Code in cell D6 as we wrote 5 as our starting character which is the first digit of the Code.

📕 Read More: 4 Ways to Remove Last 3 Characters in Excel


📝  Takeaways from This Article

📌  Firstly, we showed how to remove the last character from the string using the Flash Fill.

📌  Secondly, we demonstrated how to remove the last characters by utilizing the LEFT function.

📌  Thirdly, we applied the MID function to strip the last characters.

📌  Later, we implemented the combination of REPLACE and LEN functions to remove the very last character from a string.

📌  Next, we created a custom function by embedding a VBA code to remove the last character from the string.

📌  Lastly, we learned how to remove characters from both sides of a string merging MID and LEN functions.


Conclusion

That concludes the discussion for today. These are some convenient methods to remove the last character from a string in Excel. If you have any queries or recommendations, kindly notify us in the comments section. Downloading the practice sheet will help you comprehend the concepts better. Visit our website ExcelDen to find out about diverse kinds of Excel methods. We appreciate you taking the time to read this article.


Related Articles

(Visited 19 times, 1 visits today)
Nazmus Sakib

Nazmus Sakib

Hello, and thanks for visiting my profile. Right now I am researching Microsoft Excel, thus I will be sharing articles about myriad ways to utilize Excel effectively here. I love to watch movies and TV series in my free time. I am also a huge football fan. My priority is to continually broaden my horizons in order to enrich my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo