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
s2sdefault

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 formula in Excel

Remove first or last characters by Find and Replace in Excel

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

Supposing you have a range of text strings, and now you need to remove first, last or certain several characters from the text strings, as the following screenshots show:

                                                                                            

  doc-remove-certain-characters-6 

      Remove First Characters               Remove Last Characters              Remove Certain Characters


arrow blue right bubble 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.

Step 1. Type the following formula in adjacent cell B1: =RIGHT(A1, LEN(A1)-4), see screenshot:

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(A1, LEN(A1)-4).

Step 2. Then press Enter key, and select the cell B1, then drag the fill handle over the cells that you want to contain this formula. And now you are successful in removing the first 4 characters of the text strings. See screenshot:

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

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.


arrow blue right bubble Remove first or last characters by Find and Replace in Excel

If you want to remove all characters at the front or end of the colon :, Find and Replace function in Excel also can make your removing as soon as quickly.

Step 1. Hold the Ctrl button and press F to open Find and Replace dialog, and click Replace.

Step 2. Enter :* into the Find what box, and leave blank in Replace with box. See screenshot:

Step 3. Click Replace All, and all the characters at the end of the colon (include the colon) have been removed. See screenshot:

doc-remove-certain-characters-12

If you want to remove all characters before the colon, please type *: into the Find what box, and leave blank in Replace with box. See screenshot:

Click Replace All, all the characters before the colon have been removed. See screenshot:

Note: This method is only applied to the characters which contain the specific separators, so you can change the colon: to any other separators as your need.


arrow blue right bubble 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 120 handy Excel add-ins, free to try with no limitation in 30 days. Read More    Free Download Now

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:

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

  1. Specify the number of characters to be deleted.
  2. Choose the positions where the characters started to be deleted. You can determine the position from left or right, or from a specified position. Here we remove characters from the specified position. See screenshot:

3. The Preview pane in the right part of the box can help to see the removing result immediately. Then click OK or Apply. The specified characters have been removed from the text strings in the original range. See screenshots:

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


arrow blue right bubble 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.
    Jesus · 3 days ago
    How can I type the command to cut text from the left and the right?
  • To post as a guest, your comment is unpublished.
    Truls Sønsteby · 1 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 · 2 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 · 4 months ago
    Helpful, thanks
  • To post as a guest, your comment is unpublished.
    Jeremy · 5 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 · 2 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 · 2 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 · 3 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. · 2 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)