How to extract last two words from text strings in Excel?
This article, I will introduce some formulas to extract the last two words from a list of text strings as below screenshot shown.
Extract the last two words from a list of text strings with formulas
Extract the last two words from a list of text strings with formulas
For dealing with this job, please apply the following formulas:
Please copy and paste the below formula into a blank cell:
=MID(A2,FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1))+1,100)
And then, drag the fill handle down to the cells that you want to use this formula, and all the last two words have been extracted as following screenshot shown:
Notes:
1. In the above formula, A2 is the cell that you want to extract the last two words from;
2. Here is another simple formula which also can help to solve this problem: =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",60)),120)).
More relative articles:
- Extract All But First / Last Word In Excel
- To extract all words from a cell but the first or the last word can help you to remove the unwanted word you need, in this case, of course, you can copy the wanted words and paste them in another cell one by one. But, this will be bored if there are multiple cell values need to be extracted except the first or last word. How could you extract all words except the first or last in Excel quickly and easily?
- Extract Date From Text Strings In Excel
- In Excel worksheet, how could you extract the date from text strings as following screenshot shown? This article, I will talk about a useful formula to solve it.
- Extract First / Last / Nth Word From Text String In Excel
- Have you ever suffered with the problem that you need to extract a certain word from the text string in a worksheet? For example, you have the following range of text strings needed to get the first/last or nth word from them, here I can talk about some effective ways for you to solve it.
- Extract The First Or Last Line From A Multi-Line Cell In Excel
- If there are multi-line texts in a cell, now, you want to extract and copy the first or the last line text from the cell as following screenshot shown. How could you deal with this job quickly and easily in Excel?
- Extract First Two Or N Words From Text String
- If you have a list of text strings which are separated by space, and now, you want to extract first three or n words from the cell value to get the following screenshot result. This article, I will introduce some formulas to extract first two or n words from text string in Excel.
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
