How to Extract All But First or Last Word in Excel

Excel is a powerful tool that offers a variety of functions for data manipulation and analysis. To extract all but first /last word in Excel, you can use a combination of text functions like FIND, LEN, and SUBSTITUTE. Here's a step−by−step guide:

Extracting All But First Word in Excel

Let us see a simple process to know how to extract all but first word in Excel.

Step 1: Consider an Excel sheet where you have a list of sentences as shown in the below image.

Now, first click on an empty cell in our case B2 and enter the formula as =RIGHT(A2,LEN(A2)−FIND(" ",A2,1)) and click enter to get all but first word result.

Empty cell > Formula > Enter

Step 2: To fill all the values, drag down from the first result using the auto−fill handle, and the final result will be similar to the below image.

Extract All But Last Word in Excel

Step 1: Click on an empty cell in our case C2 and enter the formula as =LEFT(A2,FIND("[",SUBSTITUTE(A2," ","[",LEN(A2)−LEN(SUBSTITUTE(A2," ",""))))−1) and click enter to get all but last word result.

Empty cell > Formula > Enter

Step 2: To fill all the values, drag down from the first result using the auto−fill handle, and the final result will be similar to the below image.

Conclusion

In conclusion, there are different approaches to extracting all but the first and last word in Excel. In first formula using the RIGHT and FIND functions to extract the text starting from the second word, while another formula utilizes the LEFT, FIND, and SUBSTITUTE functions to extract the text up to the second−to−last word. These formulas efficiently handle cases where the number of words varies.

By applying these formulas, you can easily extract the desired portion of the text excluding the first and last words, providing flexibility and convenience in data manipulation tasks within Excel.

Pradeep Kumar

e

Updated on: 20-Jul-2023

1K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started