# Excel formula to extract the last word in a cell

To extract the last word from the text in a cell we will use the “RIGHT” function with “SEARCH” & “LEN” function in Microsoft Excel 2010.

RIGHT:Return the last character(s) in a text string based on the number of characters specified.

Syntax of “RIGHT” function: =RIGHT (text, [num_chars])

Example:Cell A1 contains the text “Broncho Billy Anderson”

=RIGHT (A1, 8), function will return “Anderson”

SEARCH:The SEARCH function returns the starting position of a text string which it locates from within the text string.

Syntax of “SEARCH” function: =SEARCH (find_text,within_text,[start_num])

Example:Cell A2 is containing the text “Broncho Billy Anderson”

=SEARCH (“Billy”, A1, 1), function will return 9

LEN:Returns the number of characters in a text string.

Syntax of “LEN” function: =LEN (text)

Example:Cell A1 contains the text “Broncho Billy Anderson”
=LEN (A1), function will return 22

Understanding the process of Extracting Characters from Text Using Text Formulas

Example 1: We have a list of Names in Column “A” and we need to pick the Last name from the list. We will use the “LEFT” function along with the “SEARCH” function.

To separate the last word from a cell follow below steps:-

• Select the Cell B2, write the formula =RIGHT(A2,LEN(A2)-SEARCH(” “,A2,SEARCH(” “,A2,SEARCH(” “,A2)+1))) function will return the last name from the cell A2.

To Copy the formula to all the cells press key “CTRL + C” and select the cell B3 to B6 and press key “CTRL + V” on your keyboard.

• To convert the formulae into values, select the function range B2:B6, and “COPY” by pressing the key “CTRL + C”, right click of the mouse select “Paste Special”.
• The Paste Special dialog box will appear. Click on “Values” then click on ok to convert the formula into values.

This is how we can extract the last word in a cell into another cell.

## 8 thoughts on “Excel formula to extract the last word in a cell”

1. “Greetings,
I am trying to see if there is a way to join two different colums of text into on colum? I am trying to populate my web store with these products from my distributor. One colum has the product name such a ARG12 and then the second colum has “”noimage.gif”” but I changed “”noimage.gif”” to just “”.gif”” now if i could only find a way to merge or joing these two colums, then the image names would all be easily imported (there over 2000) and having to manually type a “”.gif”” after each one would take forever…There must be a way to merge two colums of text? or a way to easily do this so that i end up with ARG12.gif in one colum?
Anyone?
Thanks,
Taylor reaume

• An easier way to concatenate is just using the = sign.

=(cell)&(cell) ie =A2&B2

To include spaces or constants use “” around the value
=A2&” – “&B2

2. “you might want to concatenate or ‘join’ those cells. if you’re just working with text files, its quite easy.
example:
you have “”ARG12″” in A1, and “”.gif”” in B1
=CONCATENATE(A1,B1)
this would result to: “”ARG12.gif””
if all those texts are on just TWO columns, you’d want to use this formula instead so that excel can have a reference point:
=CONCATENATE(\$A1,\$B1)
hope this helps.
The Mace

• Sure can. Using the left and right described elsewhere and earlier. For example, the first letter is easy, if it’s the first character, that is.
=LEFT(A1,1). – assuming your data is in A1

The last letter would be…
=RIGHT(A1,1)

Finally, you just concatenate these together …

=LEFT(A1,1),RIGHT(A1,1)
Or
=CONCATENATE(LEFT(A1,1),RIGHT(A1,1))

3. The solution suggested here works only for 3 column data.
Imagine a list of Names from an address list with words separated by 1 or more spaces
Names can be very variable and some lines might be blank and some names might be one word only e.g.
J Smith
John S Doe

Mr & Mrs A B C Smithson
Harry
This formula will extract the last word in all the above cases.
“Smith”, “Doe”, “”, “Smithson”, “Harry”
=IF(ISERR(FIND(” “,A2)),IF(LEN(A2)=0,””,A2),RIGHT(A2,LEN(A2)-FIND(“*”,SUBSTITUTE(A2,” “,”*”,LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))))))
Yes, one formula with SIX parentheses at the end!
The only spaces in the formula are between double quotes – there are 4 of them
It can be adapted to ignore empty lines or single word lines.

gs