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 cellB1: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!
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?
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.
Thank you so much!!!
I want to use this formula where my text is begin with some particular character or text. Can anyone help?
Thanks – This is exactly what I was looking for!
Glad to be of help!
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?
Thank you so much!!!
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)
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!
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)?
I am looking to find out the formula to takeout some text in condition format from to where..Please help me.
thanks in advance
Is there a way to KEEP the 8 last numbers ONLY?
Thankx
how to put that formula? where to put that formula
tell me you are kidding!!!
Thank U, I’ve looking for this for A decade
Thanks, this was super-helpful!
Awesome! this is exactly what I needed…Thank you!
Very Very helpful, thanks.
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?
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!
you can put the formula of =RIGHT(B1,LEN(B1)-2)
how can remove a comma that’s in a1 to a100,the comma is in every rows
Where in gods name to you put the formula?? newbie to exell
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.
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.
Hi I like to remove the letter L in this string of character, any idea what formula to use?
81908L-NPTQ
For this kind of thing, I use “find and replace” where I “find” the character and “replace” it with nothing.
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.
you make it look so easy, thanks a lot
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.
Thanks
Thanks – This is exactly what I was looking for :)
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
isn’t it easier to use the “LEFT” function?
what if it’s a variable length of data in column?
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.
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
Please assist me
I haven’t been able to get this formula to work at all. I have this character string, Q258MPE215950100, and I want to remove all characters and leave just the last 3 numbers. The original text is in column C so I inserted a column next to it. My formula looks like this =RIGHT(C2, LEN(C2)-13)
That’s correct.
It works for me. https://uploads.disquscdn.com/images/7e192aec7565935fb306f4dd5d721d2f0f9ae07a6cd398f16fc0557360c40c59.jpg