Removing the first X number of characters from a string in Excel and Google Spreadsheets

Occasionally you need to convert a specific string to remove some x number of characters at the beginning of the string. There is a very nifty function you can use for this: RIGHT()

RIGHT() returns the last X characters of a certain string, based on a number of characters you specify. Like: RIGHT(text,num_chars)

Say you want to remove the first 2 characters from a string in cell B1 you can use:

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

Or in more general terms:

=RIGHT(B1,LEN(B1)-[number of characters to remove])

How does this RIGHT() function work?

Say for example that in cell B1 you have a string “USmarketsize” and you want to remove the first two letters (“US”):

  • LEN(B1) returns the length of the string in cell B1: 12 letters
  • It then subtracts 2, in order to leave out the first 2 characters: 12-2=10
  • Then RIGHT() takes the last 10 letters from the string and returns: marketsize
  • In effect, this has removed the first 2 characters of the string

There you go! Let me know in the comments when you have any questions – I’d love to help out!

  • Rachel Porter

    How about if I have a data set where there are 100 plus text values that say “XX XXX (xx)”. With XX XXX being a different number of letters for each cell and the xx being a number within the ( ) that could be 1-3 digits long. How would I make a formula to just get the “(xx)” to appear in the adjacent cell?

    • Hi Rachel, thanks for your question.

      This should work: =MID(A1,(SEARCH(“(“,A1)+1),((SEARCH(“)”,A1)-(SEARCH(“(“,A1)+1))))

      Make sure your original string is in cell A1 for it to work.

      It searches for the ( and ) sign and gets the index number of that sign. Then it puts those index numbers in the MID() function to extract what’s in between.

      Did it work for you?

      • Rachel Porter

        Thanks so much! It did work. Is there a formula I can then apply to the original subset to remove the “(xx)” so there is one column with XXX and the following with (xx)?

        • Sure, try this: =SUBSTITUTE(A1,”(“&MID(A1,(SEARCH(“(“,A1)+1),((SEARCH(“)”,A1)-(SEARCH(“(“,A1)+1))))&”)”,””)

          This replaces the (XX) part in the original cell with the empty string “” in effect removing it.

          • Rachel Porter

            Thank you so much!!!

  • justme10

    Thanks – This is exactly what I was looking for!

  • Rochell Anthony Tice

    I have a column of text that has all different words/letters. I need to convert the entire column to only show the first 4 characters. I found how to do it with one cell using LEFT but I cannot figure out how to apply for the entire column with re-entering the formula on every cell. Help!

    • Click the cell in which you have your formula that works. Then select the right hand corner of the cell and drag your mouse down. It should fill the cells with the same formula.

      Does it work like that?

  • Nicolas Ibarra

    Thank you so much!!!

  • Kat

    Hi. I have a set of text (its length varies, but what stays consistent is the # of dashes). I’d like to drop everything including the 6th dash (starting from left). What formula would you recommend?

    XX-XX-XXXXXX-XXXX XXXXX-2015-XX-XXXXXX XX XXX XX-XX (xxx x/xx/xx)
    Thank you in advance for your help!

    • If I understand correctly you want it to return XXXXXX XX XXX XX-XX (xxx x/xx/xx)?

      If so, try this one: =RIGHT(A1,FIND( “|”,SUBSTITUTE(A1,”-“,”|”,6))+1)

      • Kat

        Hi. I tried it and unfortunately it doesn’t keep all the results the same. I tweaked it based off the one you shared and it worked. =RIGHT(B2,LEN(B2)-FIND(“-xx”,B2)) Thank you!

  • Dan

    I am looking to find a formula to shorten a postcode to the ‘area’, i tried using the above formula but ‘left’ =LEFT(U2,LEN(U2)-6) however some postcodes are a different number of characters. Is there anything i could use that looks to the ‘number’ instead of having to input a set number (-6) in my example?

    • Hi Dan,

      Couldn’t you check for the length of the text first with the IF() formula?

      Something like: IF(len(text)=X,do this,else do this)?

      • ramu kk

        I am looking to find out the formula to takeout some text in condition format from to where..Please help me.
        thanks in advance

  • jacobo

    Is there a way to KEEP the 8 last numbers ONLY?

  • Venkatesulu V

    Thankx

  • how to put that formula? where to put that formula

  • Moses Koukou

    Thank U, I’ve looking for this for A decade

  • TobyZiegler

    Thanks, this was super-helpful!

  • Lisa Bentley

    Awesome! this is exactly what I needed…Thank you!

  • Nerl

    Very Very helpful, thanks.

  • Jessica

    Hello, thanks for for the formula above! I have one follow up question:

    I’m working with a set of last names – some of the last names have middle initials at the beginning of the cell (e.g. “T. Smith”). When i applied the =RIGHT(B1,LEN(B1)-2) it worked and returned “Smith” in the adjacent cell.

    However, there are instances where the last name doesn’t have an initial at the beginning of the cell (e.g. “Smith”) and since it i dropped down the formula down the whole column it returned only “ith”. How can I adjust the formula so that it only applies to last names with an initial at the beginning of the cell?

  • Donatelo Concepcion

    Hi, pls help regarding on how to remove / on the first entry. Example /123456/234567, i wanted to remove / only at the begining of numbers except the remaining / that contains separator of numbers. Just the first / only.
    Hoping for your reply. Thank you!

    • Tushar Joshi

      you can put the formula of =RIGHT(B1,LEN(B1)-2)

  • ajesh

    how can remove a comma that’s in a1 to a100,the comma is in every rows

  • michele

    Where in gods name to you put the formula?? newbie to exell

  • Harris

    If SUM result of two cells like A1 and C1 is 1234 in Cell B2, then I need to remove first three characters from the result leaving only digit 4 in the same cell B2. If possible please help me to write down the formula for the same. Thank you so much for your help.

  • Harris

    If SUM result of two cells like A1 and C1 is 1234 in Cell B2, then I need to remove first three characters from the result leaving only digit 4 in the same cell B2. If possible please help me to write down the formula for the same. Thank you so much for your help. ALREADY SOLVED. THANK YOU.

  • Rachel Lim

    Hi I like to remove the letter L in this string of character, any idea what formula to use?
    81908L-NPTQ

    • Janelle S

      For this kind of thing, I use “find and replace” where I “find” the character and “replace” it with nothing.

  • AdrianRasmussen

    Had to use semicolon, like this:
    =RIGHT(B2; LEN(B2)-10)
    And then it worked.

  • Mrquick

    you make it look so easy, thanks a lot

  • Kazi Tanvir

    I have this type of lots of numbers which starting from “0” example; 022.98577 in Excel sheet. But I want to change this 22.98577. another is my error data is 9013691 without any dot. I want to change as 90.13691.. I have lots of this type of data.Please any body can solve my problem. It will be very much appreciable. Please let me inform.

  • Bush

    Thanks