How to Extract Last Word in Microsoft Excel

 

If you are required to extract last word from a string in excel, then you can use combination of various functions. In this article, we will focus on extracting last word & last occurrence of specific character from a cell.

 

Question: How can I extract the last word as the data I have contains separator as “space”, “comma”, “dash” or any other thing.

For more information on question you can click on this link: Extract Last Word

 

Following is the snapshot of preferred output data:

 

img1

 

We will find the same solution with different formulas.

Let us first find the solution using array.

  • This formula has the combination of TRIM, RIGHT, LEN, MAX, ROW, INDIRECT & MID functions
  • In cell C2, the formula is
  • {=TRIM(RIGHT(A2,LEN(A2)-MAX(ROW(INDIRECT(“1:”&LEN(A2)))*(MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)=”,”))))}
  • Copy the formula to get the desired output

 

Note: this is an array formula; therefore, use CTRL + SHIFT + ENTER keys together.

 

img2

 

Let us see the second solution.

  • This formula has the combination of TRIM, SUBSTITUTE, RIGHT &REPT functions
  • In cell D2, the formula is
  • =TRIM(SUBSTITUTE(RIGHT(SUBSTITUTE(A2,”,”,REPT(“,”,LEN(A2))),LEN(A2)),”,”,””))
  • Copy the formula to get the desired output

 

img3

 

Let us see the third solution.

  • This formula has the combination of TRIM, RIGHT, SUBSTITUTE & REPT functions
  • In cell E2, the formula is
  • =TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A2,”,”,” “),” “,REPT(” “,256)),256))
  • Copy the formula to get the desired output

 

img4

 

Let us see the fourth solution.

  • This formula has the combination of TRIM, RIGHT, SUBSTITUTE & REPT functions
  • In cell F2, the formula is
  • =TRIM(RIGHT(SUBSTITUTE(A2,”,”,REPT(” “,250)),250))
  • Copy the formula to get the desired output

 

img5
 
Let us see the fifth solution.

  • This formula has the combination of TRIM, RIGHT, LEN, FIND & SUBSTITUTE functions
  • In cell G2 the formula is
  • =TRIM(RIGHT(A2,LEN(A2)-FIND(“|”,SUBSTITUTE(A2,”,”,”|”,3))))
  • Copy the formula to get the desired output

 

img6

 

In this way, we can use the formula we are comfortable with & get the solution.

 

Let us take another example in which it is required to find the last occurrence of a specific character.

 

Question: How can I know the position of the last occurrence of any special character say dot, comma, back slash, etc.

Following is the snapshot of the column A that has multiple “\” & the formula needed to find the position of the last occurrence of back slash (\)

 

img7

 

  • To get the position of the last \, you would use this formula in cell B2:
  • =FIND(“@”,SUBSTITUTE(A2,”\”,”@”,(LEN(A2)-LEN(SUBSTITUTE(A2,”\”,””)))/LEN(“\”)))

 

img8

 

Formula explanation:

  • The above formula tells us the right-most “\” is at character/position 23, 15 & 9.
  • We use find for “@” and substitute the very last “\” with “@”. It determines the last one by using
  • (len(string)-len(substitute(string, substring, “”)))\len(substring)

In the above scenario, the substring is basically “\” which has a length of 1, so we can leave at the end and use the following formula:

  • =FIND(“@”,SUBSTITUTE(A1,”\”,”@”,LEN(A1)-LEN(SUBSTITUTE(A1,”\”,””))))

 

Conclusion: In this way, we can extract last word & last occurrence of the specific character.

 

image 48 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com



Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>