A simple, at first glance, problem with a non-obvious solution: extract the last word from a line of text. Well, or, in the general case, the last fragment, separated by a given delimiter character (space, comma, etc.) In other words, it is necessary to implement a reverse search (from end to beginning) in the string of a given character and then extract all the characters to the right of it .
Let’s look at the traditionally several ways to choose from: formulas, macros, and through Power Query.
Method 1. Formulas
To make it easier to understand the essence and mechanics of the formula, let’s start a little from afar. First, let’s increase the number of spaces between words in our source text to, for example, 20 pieces. You can do this with the replace function. SUBSTITUTE (SUBSTITUTE) and the function of repeating a given character N-times – REPEAT (REPT):
Now we cut off 20 characters from the end of the resulting text using the function RIGHT (RIGHT):
It’s getting warmer, right? It remains to remove extra spaces using the function TRIM (TRIM) and the problem will be solved:
In the English version, our formula will look like this:
=TRIM(RIGHT(SUBSTITUTE(A1;» «;REPT(» «;20));20))
I hope it is clear that in principle it is not necessary to insert exactly 20 spaces – any number will do, as long as it is more than the length of the longest word in the source text.
And if the source text needs to be divided not by a space, but by another separator character (for example, by a comma), then our formula will need to be slightly corrected:
Method 2. Macro function
The task of extracting the last word or fragment from the text can also be solved using macros, namely, writing a reverse search function in Visual Basic that will do what we need – search for a given substring in a string in the opposite direction – from the end to the beginning.
Press the keyboard shortcut Alt+F11 or button Visual Basic tab developer (Developer)to open the macro editor. Then add a new module via the menu Insert – Module and copy the following code there:
Function LastWord(txt As String, Optional delim As String = " ", Optional n As Integer = 1) As String arFragments = Split(txt, delim) LastWord = arFragments(UBound(arFragments) - n + 1) End Function
Now you can save the workbook (in a macro-enabled format!) and use the created function in the following syntax:
=LastWord(txt ; delim ; n)
where
- txt – cell with source text
- delim — separator character (default — space)
- n – what word should be extracted from the end (by default – the first from the end)
With any changes in the source text in the future, our macro function will be recalculated on the fly, like any standard Excel sheet function.
Method 3. Power Query
Power Query is a free add-on from Microsoft for importing data into Excel from almost any source and then transforming the downloaded data into any form. The power and coolness of this add-in is so great that Microsoft has built all of its features into Excel 2016 by default. For Excel 2010-2013 Power Query can be downloaded for free from here.
Our task of separating the last word or fragment through a given separator using Power Query is solved very easily.
First, let’s turn our data table into a smart table using keyboard shortcuts. Ctrl+T or commands Home – Format as a table (Home — Format as Table):
Then we load the created “smart table” into Power Query using the command From table/range (From table/range) tab Data (if you have Excel 2016) or on the tab Power Query (if you have Excel 2010-2013):
In the query editor window that opens, on the tab Transformation (Transform) choose a team Split Column – By Delimiter (Split Column — By delimiter) and then it remains to set the separator character and select the option Rightmost delimiterto cut not all words, but only the last one:
After clicking on OK the last word will be separated into a new column. The unnecessary first column can be removed by right-clicking its header and selecting Remove (Delete). You can also rename the remaining column in the table header.
The results can be uploaded back to the sheet using the command Home — Close and Load — Close and Load to … (Home — Close & Load — Close & Load to…):
And as a result we get:
Like this – cheap and cheerful, without formulas and macros, almost without touching the keyboard 🙂
If the original list changes in the future, it will be enough to right-click or use a keyboard shortcut Ctrl+Alt+F5 update our request.
- Splitting sticky text into columns
- Parsing and parsing text with regular expressions
- Extracting the first words from the text with the SUBSTITUTE function