## Extract last word in cell

The formula demonstrated above in cell range C3:C9 extracts the last word from adjacent cell in column B.

I was inspired by Rick Rothstein's comment from the year 2012 when I made this formula.

If your words are longer than 200 characters change each instance of 200 in the formula above to a higher value.

The delimiting character is a blank (space character). Make sure you change that if the cell value contains a different string delimiting character.

### Explaining the formula in cell C3

The SUBSTITUTE function replaces each blank in the cell value to 200 blanks.

SUBSTITUTE(B3," ",REPT(" ",200))

becomes

SUBSTITUTE(B3," "," ") and returns "Martin Williams".

(I have shortened the string for obvious reasons.)

The RIGHT function extracts the 200 characters starting from the right side of the word (backward).

RIGHT(SUBSTITUTE(B3," ",REPT(" ",200)),200)

becomes

RIGHT("Martin Williams",200) and returns " Williams".

The TRIM function removes all leading and trailing spaces in a string or cell value.

TRIM(RIGHT(SUBSTITUTE(B3," ",REPT(" ",200)),200))

becomes

TRIM(" Williams") and returns Williams in cell C3.

### Download Excel *.xlsx file

How to extract numbers from a cell value

The following array formula, demonstrated in cell C3, extracts all numbers from a cell value: =TEXTJOIN(, 1, TEXT(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), […]

The formula in cell C3 grabs the first word in B3 using a blank as the delimiting character. =LEFT(B3,SEARCH(" ",B3)-1) […]

Text to columns: Split words in a cell [Array formula]

This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]

The REPT function repeats a specific text a chosen number of times. Formula in cell D3: =REPT(B3,C3) Excel Function Syntax […]

DATEVALUE function not working

The DATEVALUE function returns an Excel date value (serial number) based on a date stored as text. However, it must […]

Extract unique distinct values based on the 4 last characters

Sean asks: The good thing about this formula is that it is short and easy to remember. The main drawback […]

The RIGHT function extracts a specific number of characters always starting from the right. Excel Function Syntax RIGHT(text,[num_chars]) Arguments text […]

Count how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]

Count specific text string in a cell

Question: How do I count how many times a text string exists in a cell value in Excel? Answer: The […]

Count text string in a range (case sensitive)

Question: How do I count the number of times a text string exists in a column? The text string may […]

Text to columns: Split words in a cell [Array formula]

This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]

The picture above shows you values that have multiple leading and trailing blanks, sometimes also between words. The TRIM function […]

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form