Method 1 – Using RIGHT Function to Get a Substring Until Space
Step 1:
- Write down the below formula in cell C5.
=RIGHT(B5,LEN(B5)-SEARCH(" ",B5))SEARCH(" ", B5)this portion finds the space from the Full name cells.- Then
LEN(B5)-SEARCH(" ", B5)this portion will select the last part of the name. - Then the
RIGHTfunction will return the selected portion.
- Press Enter on your keyboard. You will get Parks as the return of the RIGHT function.
Step 2:
- AutoFill the RIGHT function to the rest of the cells in column C.
Method 2 – Extract a Substring Using the RIGHT, LEN, SEARCH, and SUBTITUTE Functions
Steps:
- Enter the formula in cell D5 and AutoFill it up to D12.
=RIGHT(C5,LEN(C5)-SEARCH("$",SUBSTITUTE(C5,":","$",LEN(C5)-LEN(SUBSTITUTE(C5,":","")))))Steps: Step 1: Step 2: Step 1: Step 2: Steps: Download Practice Workbook Download this practice workbook to exercise while you are reading this article. << Go Back to Excel Functions | Learn Excel
LEN(SUBSTITUTE(C5,":","")) this portion finds the colon (:) sign in the whole string.SUBSTITUTE(C5,":","#",LEN(C5)-LEN(SUBSTITUTE(C5,":",""))) this part replaces the last delimiter with some unique character.SEARCH("#", SUBSTITUTE(C5,":","#",LEN(C5)-LEN(SUBSTITUTE(C5,":","")))) this part gets the position of the last delimiter in the string. Depending on what character we have replaced the last delimiter with, use either case-insensitive SEARCH or case-sensitive FIND to determine the position of that character in the string.RIGHT function selects comments and prints them.
Method 3 – Remove First N Characters from a String Applying RIGHT Function
=RIGHT(C5, LEN(C5)-10)
LEN(C5)-10 will return a number after subtracting 10 from the total characters number. If the total length is 25 then this portion will return 25-10 = 15.RIGHT function will return the only comment from the source comment.
Method 4 – Utilizing RIGHT and VALUE Functions to Extract Number from a String
=VALUE(RIGHT(D5, 5))
RIGHT(D5, 5) this portion gives the 5 characters from the address which is the zip code in text format.VALUE function converts them into a number format.
Method 5 – Applying RIGHT, LEN, and FIND Functions to Extract Domain Name from Email
=RIGHT(E5,LEN(E5)-FIND("@",E5))
FIND("@",E5) this portion finds @ from the given string.LEN(E5)-FIND("@", E5) will give the number up to which the value will be extracted.
Method 6 – Using RIGHT, LEN, and LEFT Functions to Modify URL
=LEFT(B5,LEN(B5)-(RIGHT(B5)="/"))
(RIGHT(B5)=”/”) returns “true,” or else it returns “false”.=LEFT(B5, LEN(B4)-(RIGHT(B5)=”/”)) returns the first “n” number of characters. If the last character is a forward slash (/), it is omitted; else, the complete string is returned.
Special Notes for Using RIGHT Function of MS Excel
Does the RIGHT function return number?
The RIGHT function in Excel always produces a text string, despite the fact that the initial value was a number, as was stated at the beginning of this lesson.The RIGHT function can not work with dates?
Since dates are represented by integers in the internal Excel system and the Excel RIGHT function is built to operate with text strings, it is not possible to extract a specific part of a date, such as a day, month, or year. If you try this, all you will receive are the final few digits of a number that represents a date.Why the RIGHT function returns #VALUE error?
The RIGHT function returns #VALUE! error if “num_chars” is less than zero.
Hi – I am trying to write a formula to extract the name and address from these strings. My thought was to try to do an IF statement since they either start with PRM-OC-SF-Note or PRM-SF-Deed of Trust. Something like IF cell = “PRM-OC-SF-Note” then remove the first 42 characters but IF cell = “PRM-SF-Deed of Trust” then remove the first 53 characters. Then I can do a comma delimited for the rest. But I am not sure how to combine and IF function with a Left/Right trim. Also not sure if there is a better formula
Client Column
PRM-OC-SF-Note – 5/28/2010 – 0001111111- DOE – JANE – 111 WINDY AVE – SINGLE FAMILY – OMITTED – – – – -144444444
PRM-SF-Deed of Trust – 11/11/2008 – 0001111111 – DOE – JANE – 111 WINDY AVE – SINGLE FAMILY – – – – – – -1444444444
PRM-OC-SF-Note – 8/18/2008 – 0033333333 – DOE – JIM – 1222 OXFORD CIR – SINGLE FAMILY – – – – – -1433333333
PRM-SF-Deed of Trust – 7/1/2008 – 0033333333 – DOE – JIM – 1222 OXFORD CIR – SINGLE FAMILY – PAID OFF – – – – – 1593-1433333333
PRM-OC-SF-Note – 9/15/2009 – 0034444444- SMITH – JOHN – 333 ELMWOOD PKWY – GINNIE MAE – SECURITIZED – 0222222222 – 0000000000 – 11/3/2009 – 5/20/2010-1455555555
PRM-SF-Deed of Trust – 7/8/2014 – 0034444444- SMITH – JOHN – 333 ELMWOOD PKWY – GINNIE MAE – SECURITIZED – 0222222222 – – – – -1455555555
Hi, NOHEMI WEST-PHELPS! Can you please email us the Excel file containing the problem with the dataset?