How To Remove First, Last X Characters Or Certain Position Characters From Text In Excel?

For most of Excel users, sometimes, you need to delete first n characters from the beginning of the text strings or remove the last x characters from the end of the text strings as below screenshot shown. This article, I will introduce some handy tricks for solving this task quickly and easily in Excel.
 

doc remove first x characters 1


Method 1: Remove first or last x characters from text strings with formulas

 Remove first x characters from the beginning of the text strings:

In Excel, you can use the RIGHT and LEFT functions to remove the certain number of characters from the beginning or the end of the strings, please do as this:

1. Type or copy the following formula in a blank cell C4 where you want to put the result:

=RIGHT(A4, LEN(A4)-2)

and press Enter key to get the first result. See screenshot:

doc remove first x characters 2

Note: In the above formula:

  • A4 is the cell value that you want to remove characters;
  • The number 2 means the number of characters you want to remove from the beginning of the text string.

2. Then, select the cell C4 and drag the fill handle down to the cells where you want to apply this formula, and all the first 2 characters have been removed from the text strings, see screenshot:

doc remove first x characters 3


 Remove last x characters from the end of the text strings:

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

Please enter or copy this formula into a blank cell:

=LEFT(A4, LEN(A4)-9)

and then drag the fill handle down to the cells that you want to apply this formula, and last 9 characters have been deleted from the text strings at once, see screenshot:

doc remove first x characters 4

Note: In the above formula:

  • A4 is the cell value that you want to remove characters;
  • The number 9 means the number of characters you want to remove from the end of the text string.

Method 2: Remove first or last x characters from text strings with User Defined Function

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

 Remove first x characters from the beginning of the text strings:

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(A4,2) into a blank cell, and then drag the fill handle down to get the results as you need, see screenshot:

doc remove first x characters 5

Note: In the above formula:

  • A4 is the cell that you want to remove characters;
  • The number 2 indicates the number of characters you would like to remove from the beginning of the text string.

 Remove last x characters from the end of the text strings:

To remove last n characters 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(A4,9) to get the results as you need, see screenshot:

doc remove first x characters 6

Note: In the above formula:

  • A4 is the cell that you want to remove characters;
  • The number 9 indicates the number of characters you would like to remove from the end of the text string.

Method 3: Remove first, last x characters or certain position characters without any formulas

Using the Excel functions to remove certain characters is not as directly as it is. Just take a look at the way provided in this method, which is no more than two or three mouse clicks. 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. Click to download Kutools for Excel! Please see below demo:

After installing Kutools for Excel, please 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:

doc remove first x characters 7

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 characters, see screenshot:

doc remove first x characters 8

Tips: The Remove by Position utility also can help you to remove the last n characters or the certain characters from specific position.

Method 4: Remove both first x and last x characters from text strings with formula

Sometimes, you would like to remove characters from text strings on both sides, for example, you need to remove first 2 characters and last 9 characters at the same time. Here, the MID function can do you a favor.

1. Please enter or copy this formula into a blank cell:

=MID(A4,3,LEN(A4)-11)

and press Enter key to get the first result. See screenshot:

doc remove first x characters 11

Note: In the above formula:

  • A4 is the cell value that you want to remove characters;
  • The number 3 is one more than the number of characters you want to remove from the left side;
  • The number 11 is the total number of characters you want to remove.

2. Then, select the cell C4 and drag the fill handle down to the cells where you want to apply this formula, and all the first 2 and last 9 characters have been removed immediately from the text strings, see screenshot:

doc remove first x characters 12



The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Roopesh.R · 4 years ago
    :-) Thanks for the help
  • To post as a guest, your comment is unpublished.
    Becky · 4 years ago
    What if I want to remove characters from both the left and the right?

    I'm using a barcode scanner and I want an excel formula to remove the first 7 characters from the left and the last 2 characters on the right.

    Here's what the original information looks like:

    +$$8017CAPHLESS-001A-
    +$$8017PRYOMCP-084QY
    +$$8017LM00009CR

    Here's what I want them to look like:

    CAPHLESS-001
    PRYOMCP-084
    LM00009

    Any help would be appreciated. :lol:

    Thank you!
    • To post as a guest, your comment is unpublished.
      Dave · 4 years ago
      [quote name="Becky"]What if I want to remove characters from both the left and the right?

      I'm using a barcode scanner and I want an excel formula to remove the first 7 characters from the left and the last 2 characters on the right.

      Here's what the original information looks like:

      +$$8017CAPHLESS-001A-
      +$$8017PRYOMCP-084QY
      +$$8017LM00009CR

      Here's what I want them to look like:

      CAPHLESS-001
      PRYOMCP-084
      LM00009

      Any help would be appreciated. :lol:

      Thank you![/quote]


      assuming its always the first 7 characters and the last two that you want to leave out, this will do the trick.
      =MID(A2,8,(LEN(A2)-9))
      Takes the text from the middle of the string starting at character 8, then to determine where it stops the copy it subtracts 9 (7 + 2, total number of characters that you want left out) from the total number of characters in the string.

      Hope this helps.
    • To post as a guest, your comment is unpublished.
      Ken Adams · 4 years ago
      =RIGHT(B1,LEN(B1)-7)
  • To post as a guest, your comment is unpublished.
    Robin · 4 years ago
    I'm finding for it difficult to extract *@airspan.com the following for the content "*@.com"
  • To post as a guest, your comment is unpublished.
    Azim · 4 years ago
    What should I do if I want to remove every thing from the following string except NAME

    9/13/15 11:08 AM: John Doe : Welcome to xyz

    I have 31K entries and I am trying to clean data to capture names only
    • To post as a guest, your comment is unpublished.
      Keith · 4 years ago
      I would do this in two steps just with standard find and replace (search):

      1) find "*: " and replace with ""
      2) find " :*" and replace with ""
  • To post as a guest, your comment is unpublished.
    Slamat Ale · 4 years ago
    Just awesome :) 1st time know *: or :* trick thax..
  • To post as a guest, your comment is unpublished.
    Rajesh Patel · 4 years ago
    Thanks a ton for sharing this most important formulas !
  • To post as a guest, your comment is unpublished.
    katie · 4 years ago
    what should I do if I have text like
    abc?1001, abc?a10001 and so on. And I want to remove part after "?" and I can't predict how long will be that part
    • To post as a guest, your comment is unpublished.
      Dave · 4 years ago
      [quote name="katie"]what should I do if I have text like
      abc?1001, abc?a10001 and so on. And I want to remove part after "?" and I can't predict how long will be that part[/quote]
      =MID(A2,1,SEARCH("~?",A2,1)-1) assuming your data is in cell A2.
      This will give you everything from before the question mark.
      or
      =MID(A2,SEARCH("~?",A2,1)+1,LEN(A2))
      This will give you everything after the question mark.
      When searching for "Special characters" you need to include the tilde (~) before the character.
  • To post as a guest, your comment is unpublished.
    GIRARD · 4 years ago
    COLUMN A1 IS P11-L22-333
    COLUMN B1 IS 1122333

    Please help. if A1 IS P11-L22-333. What formula will i do to make it 1122333.

    (P), (-) and (L) should be erase.
    • To post as a guest, your comment is unpublished.
      girard · 4 years ago
      pls help guys. i need the formula will i use
  • To post as a guest, your comment is unpublished.
    xavier · 4 years ago
    you are on the right track! some answers in the link below. you need to find the position of the last ";" in your string. Then doright(Len - that position of the last ";")
    http://stackoverflow.com/questions/350264/how-can-i-perform-a-reverse-string-search-in-excel-without-using-vba
  • To post as a guest, your comment is unpublished.
    Tracy Clark · 4 years ago
    Hi,

    I want to remove everything before ; from the below text, but the formula =RIGHT(A1,(SEARCH(";",A1)-1)) doesn't work. I just want to keep XBR65X850B.

    X;0;0;2;0;0;XBR65X850B

    Can anyone please help?
  • To post as a guest, your comment is unpublished.
    ApoAz · 4 years ago
    How can I insert a function that parses out the ".RCPRO999.com",".PVC999.com", and ".RVCA0887.com" and just keeps the original email. When I insert =LEFT(P4, LEN(P4)-13) it only works for for the ".RCPRO99.com" but the ending are various sizes

    amazonman153@ccrtc.com.RCPRO999.com
    maniacman999999@aol.com.PVC999.com
    anchorman87847@yahoo.com.RVCA0887.com

    Thanks
    M
    • To post as a guest, your comment is unpublished.
      Dave · 4 years ago
      [quote name="ApoAz"]How can I insert a function that parses out the ".RCPRO999.com",".PVC999.com", and ".RVCA0887.com" and just keeps the original email. When I insert =LEFT(P4, LEN(P4)-13) it only works for for the ".RCPRO99.com" but the ending are various sizes

      amazonman153@ccrtc.com.RCPRO999.com
      maniacman999999@aol.com.PVC999.com
      anchorman87847@yahoo.com.RVCA0887.com

      Thanks
      M[/quote]

      This ones a bit hazy. it all depends if your email addresses will always end with ".com" (never .co.uk or .net etc)
      assuming they will always end in .com, try this;

      =LEFT(A2,SEARCH(".com",A2,1)+3) Assuming your data is in cell A2.
  • To post as a guest, your comment is unpublished.
    tracey · 4 years ago
    Trying to remove * at beginning and end of a text string for example *XX-XXXX-XX-XX* and *XX-XXX-XX-XX* not getting any of these formulas to work keep getting circular error :(
    • To post as a guest, your comment is unpublished.
      Dave · 4 years ago
      [quote name="tracey"]Trying to remove * at beginning and end of a text string for example *XX-XXXX-XX-XX* and *XX-XXX-XX-XX* not getting any of these formulas to work keep getting circular error :([/quote]
      try this;

      =MID(A2,SEARCH("~*",A2,1)+1,SEARCH("~*",A2,SEARCH("~*",A2,1)+1)-2)
      Assuming your data is in A2 and there are only 2 *'s in your cell.
      This will look for the first *, then look for the second *, and pull everything in between.
  • To post as a guest, your comment is unpublished.
    Anoop · 5 years ago
    I tried following formula but ran into another issue:

    =LEFT(A1,FIND(" ",A1,FIND(" ",A1)+2))

    However, whenever a last name had a " " in it, this formula returned an erroneous value. (ie., "De La Vega, Guzman T" returned only "De La")

    So, I am thinking of a different logic.
    I need to check if the second last character of the string is " " then return the string before it, otherwise, keep the string as is.

    Can someone help me with an excel formula for this logic?
  • To post as a guest, your comment is unpublished.
    Anoop · 5 years ago
    Hey Guys,

    I need to extract only "Last, First" from a column containing values in "Last, First" as well as "Last, First MI"

    What's the best function to extract it. Logically, I think I need to look for the second occurrence of " " (space) in a string (say in cell A1) and return everything left of it.

    I am not able to come up with a functional arrangement to do it.
    Thanks in advance for help.
  • To post as a guest, your comment is unpublished.
    JITENDRA RAGHUVANSHI · 5 years ago
    the futnction was very helpful and because of this i could save my lagr amount of time
  • To post as a guest, your comment is unpublished.
    Abdul Gani · 5 years ago
    really helpful Thank you for posting this
  • To post as a guest, your comment is unpublished.
    Gabriela · 5 years ago
    Awesome! This was very helpful, thanks!!!
  • To post as a guest, your comment is unpublished.
    Craig · 5 years ago
    thanks, this is a great post.

    I am trying to pull the first letter of two names to automate into a action log? Can you help me with this formula? Sure it is LEN. But can't just get it....

    eg.

    Brian Adams = BA

    Thanks

    Craig
    • To post as a guest, your comment is unpublished.
      aries · 5 years ago
      try this bro A1=Brian Adams B2=CONCATENATE(LEFT(A1,1),LEFT(RIGHT(A1,SEARCH(" ",A1)-1),1))
    • To post as a guest, your comment is unpublished.
      Xavier · 5 years ago
      @Craig, you could do this :
      =CONCATENATE((MID(A2,1,1)),(MID(A2,(FIND(" ",A2)+1),1)))

      considering your source data is in cell A2.
      However this will not work if you have more than 2 distinct names separated by spaces. You could elaborate that in a IF statement to tackle cases where you have 3 names and want to extract 3 letters (i.e. John Bon Jovi....JBJ)

      cheers!
  • To post as a guest, your comment is unpublished.
    Lori W · 5 years ago
    Thank you! The formula works for my needs! :-)
  • To post as a guest, your comment is unpublished.
    Surendar · 5 years ago
    Nice tip guys. Very useful tips
  • To post as a guest, your comment is unpublished.
    karen · 5 years ago
    but what if the characters in a cell have different font colors and I want to retain their colors?
    • To post as a guest, your comment is unpublished.
      Adwait Pande · 5 years ago
      Use the format painter in that case.

      Suppose your original data is in 'column A' (which is formatted with color)
      and the data after applying formula is in 'column B' (unformatted)

      then follow the following steps:
      1) Click on 'Format Painter' from 'Home' menu
      2) Click on column name 'A' (complete column should be selected)
      3) Click on column name 'B'

      You will have same formatting for column B as your source column (column A).

      Hope this helps.
  • To post as a guest, your comment is unpublished.
    katrina_rose · 5 years ago
    Hi guys,

    set up a list with 3 columns and about 500 rows, and in every cell I have a"#" that has to be removed.
    I really dont understand how to remove it and dont get on with the formula on top.
    May it be because I'm using a mac device?
    • To post as a guest, your comment is unpublished.
      Xavier · 5 years ago
      just do a find and replace.
      Find "#" replace with "" blank.
      That way you don't even need a formula.This is considering there is only one "#" you'd like to remove in each cell.

      cheers!
  • To post as a guest, your comment is unpublished.
    Ray · 5 years ago
    Hi! Hope you can help me guys how to remove any characters after first comma from Left and add
    • To post as a guest, your comment is unpublished.
      Xavier · 5 years ago
      Hi #Ray,

      Your question seems like it's missing some details at the end, but to remove anything after the first coma, you can use the following:

      assuming your source data is in A1.
      Basically, grabs everything left of the first coma it finds in the string (position of the coma minus 1)

      =LEFT(A1,(SEARCH(",",A1)-1))

      if A1 is abcdef,ghijkl then you will get abcdef
  • To post as a guest, your comment is unpublished.
    kn · 5 years ago
    I have a group of numbers. Some of them have a zero at the beginning. I can't have a zero as the first number. The numbers are not all the same length but I just want the zero gone. Is there a way to do that.
    • To post as a guest, your comment is unpublished.
      Xavier · 5 years ago
      @kn :

      you could use the following, considering the source cell is A1
      In the formula below, if will evaluate what the first character is, and if it's a zero, it will remove it, if it is not a zero, it will remain as is.

      =IF((LEFT(A1,1)="0"),RIGHT(A1,(LEN(A1)-1)),A1)

      hope this helps, cheers!
  • To post as a guest, your comment is unpublished.
    Abdul Gani · 5 years ago
    Thank you team .... really helpful
  • To post as a guest, your comment is unpublished.
    spaha · 5 years ago
    Great tip! Very helpfull for beginners like me! Thanks!
  • To post as a guest, your comment is unpublished.
    Swapnil · 5 years ago
    THanks a lot was really helpful!
  • To post as a guest, your comment is unpublished.
    Nishant Shastry · 5 years ago
    Excellent tip! You guys just made my life so much easier :)
  • To post as a guest, your comment is unpublished.
    Rahul SIngh · 6 years ago
    Thaks for helping me
  • To post as a guest, your comment is unpublished.
    Happy · 6 years ago
    this is what i am looking for..
    I need one more thing:
    I want to copy the values which are in brackets into another cell, in your case- Postcode (ZIP) : 211230, want to copy ZIP into another cell.
    • To post as a guest, your comment is unpublished.
      Wisdom Singer · 5 years ago
      [quote name="Happy"]this is what i am looking for..
      I need one more thing:
      I want to copy the values which are in brackets into another cell, in your case- Postcode (ZIP) : 211230, want to copy ZIP into another cell.[/quote]
      Hi, Happy. Did you ever get an answer? If not, easiest way would be with the Mid command. For example, if Postcode (ZIP): 211230 is in Cell A1, the command would be =MID(A1,11,3) - i.e., take the middle 3 characters starting with the 11th one from the left.
      • To post as a guest, your comment is unpublished.
        ZEDD · 5 years ago
        I have 275 names but in a repeated manner. I want to extract each name from that list which will appear only once.
        • To post as a guest, your comment is unpublished.
          ankit · 3 years ago
          select the specific column and in "data" panel.. click "remove duplicates"
        • To post as a guest, your comment is unpublished.
          Uma Kanth · 3 years ago
          Hi Zedd,

          For the task mentioned by you. We can use IF and COUNT IF combination Ex: =IF(COUNTIF($A$1:$A$275,A1)= 1,A1,FALSE)

          Regards,
          Umakanth Ramineedi
  • To post as a guest, your comment is unpublished.
    Holly · 6 years ago
    Thank you for posting this!! :-)
  • To post as a guest, your comment is unpublished.
    fwfceas · 6 years ago
    thank u dear for this help
  • To post as a guest, your comment is unpublished.
    Steve Jadrnak · 6 years ago
    Print this item to help with removing characters.
  • To post as a guest, your comment is unpublished.
    prasad · 6 years ago
    its use full to make data much easier

    Thanks
    • To post as a guest, your comment is unpublished.
      Aleksandar · 4 years ago
      Sorry, but doesn't work for me. Shows me an error.