Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or
0
0
0
s2smodern

How to remove first, last or certain characters from text in Excel?

We may use Excel functions to remove characters from a text string in Excel. This article is going to talk about the solutions to remove certain characters from a text string.

Remove first or last characters by formulas in Excel

Remove first or last characters by User Defined Function in Excel

Remove first, last, or certain characters with Kutools for Excel


Remove first, last or certain characters from a list of text strings:

To delete first or last n characters from a list of text strings, Kutools for Excel's Remove by Position utility can help you to solve this task as quickly as possible in Excel worksheet.

delete first last n characters 9

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Remove first or last characters by formula in Excel


With the Excel LEFT and RIGHT function, you can remove the certain characters from the beginning or the end of the strings. Please do as the following steps:

Remove the first four characters from the text string:

Type the following formula in a blank cell C2 where you want to put the result:=RIGHT(A2, LEN(A2)-4), and then drag the fill handle down to the cells that you want to fill this formula, and the first 4 charatcers have been removed from the text strings, see screenshot:

delete first last n characters 1

Tips: This formula means to return the right most number of characters, you need to subtract 4 characters from left string. And you can specify the number of characters you want to remove from the left string by changing the Number 4 in the formula =RIGHT(A2, LEN(A2)-4).


Remove the last six characters from the text string:

If you need to remove the last several characters, you can use the LEFT function as the same as the RIGHT function.

Please enter this formula:=LEFT(A2, LEN(A2)-6) into a blank cell, and then drag the fill handle down to the cells that you want to apply this formula, and last 6 charctaers have been deleted from the text strings at once, see screenshot:

delete first last n characters 2

Note: Using the Excel function to remove certain characters is not as directly as it is. Just take a look at the way provided in next method, which is no more than two or three mouse clicks.


Remove first or last characters by User Defined Function in Excel

Here is a User Defined Function which also can help you to remove first or last n charaters from text strings, please do as this:

Remove the first four characters from the text string:

1. Hold down the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

Public Function removeFirstx(rng As String, cnt As Long)
removeFirstx = Right(rng, Len(rng) - cnt)
End Function

3. Then go back to the worksheet, and then enter this formula: =removefirstx(A2,4) into a blank cell, and then drag the fill handle down to get the results as you need, see screenshot:

delete first last n characters 3

Notes:

1. In the above formula, A2 is the cell that you want to remove charaters, and the number 4 indicates the number of charaters you would like to remove.

2. To remove last n charaters from the text strings, please apply the following User Defined Function:

Public Function removeLastx(rng As String, cnt As Long)
removeLastx = Left(rng, Len(rng) - cnt)
End Function

And then apply this formula: =removelastx(A2,6) to get the rsults as you need, see screenshot:

delete first last n characters 4


Remove first, last, or certain characters with Kutools for Excel

With the Remove by Position utility of the third party add-in Kutools for Excel, you can be easy to remove first, last or certain characters from the text string in one click.

Kutools for Excel : with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. 

After installing Kutools for Excel, apply Remove by Position according to these steps:

1. Select the range that you want to remove the certain characters. Then click Kutools > Text > Remove by Position. See screenshot:

delete first last n characters 5

2. Specify the following operations in the pop-up Remove by Position dialog box. 

  1. Specify the number of characters to be deleted.
  2. Choose From left option under the Position section to delete the first n charaters, see screenshot:

delete first last n characters 6

Tips:

1. For removing the last n characters, please apply the following operations in the dialog box, see screenshot:

delete first last n characters 7

2. For removing the certain characters at specified position, please apply the following operations in the dialog box, see screenshot:

delete first last n characters 8

Note: To avoid non-text cell, please choose Skip non-text cell.


Remove first, last, or certain characters with Kutools for Excel

Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 60 days. Download the free trial now!


Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
People in conversation:
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Truls Sønsteby · 7 months ago
    I can't make this formula work, I type : =LEFT(A1,LEN(A1)-4) and it gives error everytime.
    My A1 column has the following data: ELDB-AK-S-42228-001


    Why isn't this working?
  • To post as a guest, your comment is unpublished.
    Myke · 8 months ago
    i have a list of sizes that have numbers and names, words,etc. i want to remove all letters in the entire column leaving only the numbers. how do i remove all letter which come in hundreds of different variations and lengths?
  • To post as a guest, your comment is unpublished.
    Muhammad Awais Youna · 10 months ago
    Helpful, thanks
  • To post as a guest, your comment is unpublished.
    Jeremy · 11 months ago
    Hi there everyone,
    I am trying to remove an initial in a field that includes a persons name. An example is

    Taylor Jeremy D In this example I am trying to remove the D and just leave surname and first name there.

    The problem is that I have a heap of data which is inconsistently entered meaning that some of the fields have an initial (like that above) and some just say a name like Taylor Jeremy (without an initial).



    Any ideas so that I can have a consistent data set and one that excludes the initial?
    • To post as a guest, your comment is unpublished.
      Jorge · 8 months ago
      Try this, it solves exactly the way you want it.


      =IF((IFERROR((FIND(" ",A1,((FIND(" ",A1,1))+1)))/(FIND(" ",A1,((FIND(" ",A1,1))+1))),0))=1,LEFT(A1,((FIND(" ",A1,((FIND(" ",A1,1))+1)))-1)),A1)



      A1 is Taylor Jeremy D or without the D, as you wish
    • To post as a guest, your comment is unpublished.
      Jorge · 8 months ago
      Just for lettiing you know that I solved your problem, I hope im not too late.

      =SI((SIERREUR((TROUVE(" ";R9;((TROUVE(" ";R9;1))+1)))/(TROUVE(" ";R9;((TROUVE(" ";R9;1))+1)));0))=1;GAUCHE(R9;((TROUVE(" ";R9;((TROUVE(" ";R9;1))+1)))-1));R9)



      Cell R9 is your Taylor Jeremy D, try it with and without the D and you'll see it works fine
    • To post as a guest, your comment is unpublished.
      Sigma · 9 months ago
      If the text name (Taylor Jeremy D) located in A1, you can use below function to acheive the result (remove the last name):
      =LEFT(A1,FIND(" ",A1,1))&LEFT(RIGHT(A1,LEN(A1)-A2),FIND(" ",RIGHT(A1,LEN(A1)-A2))-1)
      • To post as a guest, your comment is unpublished.
        Alex B. · 8 months ago
        Can also be accomplished with the below string, which is simpler and doesn't require formulas in multiple cells. Following same assumption that data is in A1 and the initial is always 1 character. TRIM function added within the LEN function in case there are unseen trailing spaces in the data. TRIM function added at the beginning of the formula will delete any residual spacing after the LEFT function is executed.

        =TRIM(LEFT(A1,LEN(TRIM(A1))-1))

        Or if you don't like the leading TRIM function you can subtract 2 characters from the LEN function instead of just the one. I prefer TRIM route in case there are more than 1 spaces in between the name and middle initial - have to account for inconsistent data entry.

        =LEFT(A1,LEN(TRIM(A1))-2)
  • To post as a guest, your comment is unpublished.
    pejot · 11 months ago
    Hi there,
    im looking for a solution. Problem is that i have two colums of data where 1st column contain some text that part of is invalid and need to be removed, 2nd column contain invalid text string.


    Let's say cell A1 contain text string "ABCDEF"

    Cell A2 contain invalid part of the string, for example "CD"


    After removing invalid text string, cell A1 will look like this: "ABEF".

    How to do that? Thanks in advance!
    • To post as a guest, your comment is unpublished.
      Gaurav · 11 months ago
      Hey you can do this with a combination of functions:


      =CONCATENATE(LEFT(Y4;FIND("c";Y4)-1);RIGHT(Y4;LEN(Y4)-FIND("c";Y4)-1))



      Concatenate brings the two strings together, because you want to take a left side of the string and combine it with a right side of the string eliminating the middle values.