# 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!!!

• Priyabrata Hota

I want to use this formula where my text is begin with some particular character or text. Can anyone help?

• justme10

Thanks – This is exactly what I was looking for!

• Glad to be of help!

• 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)

• 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.

• 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

• Curter

tell me you are kidding!!!

• Moses Koukou

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

• TobyZiegler

• Lisa Bentley

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

• Nerl

• 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.

• 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.

• Curter

Risky solution if the column also has
81908L-NLTQ; that will turn this into 81908-NTQ
My idea is to split into separate columns, perform the removal, then join again with + or &

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

Thanks – This is exactly what I was looking for :)

• Mona Onofrei

Hi. I have this list and need create a formula that returns only the characters that appear after “X”
for the given character strings. The same formula must work for the whole column! I used this =RIGHT(B2,LEN(B2:B5)- 11) but number 11 should be also a formula with COUNT maybe, because this number changes in my below list. Thank you very much.
582473160X475
48129047X773405
89017835X35
520853124X9972

• crlos_cstillo

isn’t it easier to use the “LEFT” function?

• Curter

what if it’s a variable length of data in column?

• Manuel Zammit

Thanks a lot. I had to use this formula in a column of numbers typed in as text (Eur 1234) to convert them to numbers. I first used this formula to delete the Eur and then converted the text to numbers by multiplying the result by 1 in a column formatted to show the numbers as Euro.

• Niklaus Stone

John G. Kennedy, MD, Hospital for Special Surgery, 523 East 72nd Street, Suite 507, New York, NY 10021, USA

From above i want to remove first two comma including the words in excel. So after removal contents will be- Hospital for Special Surgery, 523 East 72nd Street, Suite 507, New York, NY 10021, USA