# Extract last word in cell

#### Table of Contents

## 1. Extract the last word

The formula demonstrated above in cell range C3:C9 extracts the last word from an 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

#### Step 1 - Repeat space character 200 times

The REPT function repeats a specific text a chosen number of times.

REPT(*text*, *number_times*)

REPT(" ", 200)

returns 200 space characters concatenated.

#### Step 2 - Substitute space character with 200 space characters

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

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

becomes

SUBSTITUTE("Martin Williams"," "," ")

and returns "Martin Williams".

(I have shortened the string for obvious reasons.)

#### Step 3 - Extract 200 characters from right

The RIGHT function extracts the 200 characters starting from the right.

RIGHT(*text*,[*num_chars*])

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

becomes

RIGHT("Martin Williams", 200)

and returns " Williams". All space characters are not shown for obvious reasons.

#### Step 4 - Remove leading space characters

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.

## 2. Extract the last letter

The formula in cell D3 extracts the last letter from characters in cell B3, the value contains letters, numbers, and other random characters.

Array formula in cell D3:

### 2.1 How to enter an array formula

The image above shows a leading curly bracket, the formula is too large to display the trailing curly bracket, however, it is there. They appear automatically when you follow the steps below.

- Copy the array formula above.
- Double press with the left mouse button on cell D3, a prompt appears.
- Paste it to cell C3, shortcut keys are CTRL + v.
- Press and hold CTRL + SHIFT keys simultaneously.
- Press Enter once.
- Release all keys.

The formula bar shows a beginning and ending curly bracket, don't enter these characters yourself.

### 2.2 Explaining formula

#### Step 1 - Search for all letters

The SEARCH function returns a number representing the position of character at which a specific text string is found reading left to right. It is not a case-sensitive search.

SEARCH(*find_text*,*within_text*, [*start_num*])

SEARCH({"a"; "b"; "c"; "d"; "e"; "f"; "g"; "h"; "i"; "j"; "k"; "l"; "m"; "n"; "o"; "p"; "q"; "r"; "s"; "t"; "u"; "v"; "w"; "x"; "y"; "z"}, B3)

becomes

SEARCH({"a"; "b"; "c"; "d"; "e"; "f"; "g"; "h"; "i"; "j"; "k"; "l"; "m"; "n"; "o"; "p"; "q"; "r"; "s"; "t"; "u"; "v"; "w"; "x"; "y"; "z"}, "12 Wi llia3 3m s2 ")

and returns

{10; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 5; #VALUE!; #VALUE!; 7; 14; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 16; #VALUE!; #VALUE!; #VALUE!; 4; #VALUE!; #VALUE!; #VALUE!}

#### Step 2 - Replace error values with blanks

The MAX function can't handle error values, we must take care of them. The IFERROR function can replace error values with a given value.

IFERROR(*value*, *value_if_error*)

IFERROR(SEARCH({"a"; "b"; "c"; "d"; "e"; "f"; "g"; "h"; "i"; "j"; "k"; "l"; "m"; "n"; "o"; "p"; "q"; "r"; "s"; "t"; "u"; "v"; "w"; "x"; "y"; "z"}, B3), "")

becomes

IFERROR({10; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 5; #VALUE!; #VALUE!; 7; 14; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 16; #VALUE!; #VALUE!; #VALUE!; 4; #VALUE!; #VALUE!; #VALUE!}, "")

and returns

{10; ""; ""; ""; ""; ""; ""; ""; 5; ""; ""; 7; 14; ""; ""; ""; ""; ""; 16; ""; ""; ""; 4; ""; ""; ""}.

#### Step 3 - Calculate the largest number in the array

The MAX function returns the largest number in a cell range or array.

MAX(IFERROR(SEARCH({"a"; "b"; "c"; "d"; "e"; "f"; "g"; "h"; "i"; "j"; "k"; "l"; "m"; "n"; "o"; "p"; "q"; "r"; "s"; "t"; "u"; "v"; "w"; "x"; "y"; "z"}, B3), ""))

becomes

MAX({10; ""; ""; ""; ""; ""; ""; ""; 5; ""; ""; 7; 14; ""; ""; ""; ""; ""; 16; ""; ""; ""; 4; ""; ""; ""})

and returns 16.

#### Step 4 - Extract the last letter

The MID function returns a substring from a string based on the starting position and the number of characters you want to extract.

MID(*text*, *start_num*, *num_chars*)

MID(B3, MAX(IFERROR(SEARCH({"a"; "b"; "c"; "d"; "e"; "f"; "g"; "h"; "i"; "j"; "k"; "l"; "m"; "n"; "o"; "p"; "q"; "r"; "s"; "t"; "u"; "v"; "w"; "x"; "y"; "z"}, B3), "")), 1)

becomes

MID("12 Wi llia3 3m s2 ", 16, 1)

and returns "s" in cell D3.

## 3. Extract the last digit in a cell

The image above demonstrates an array formula in cell D3 that extracts the last digit in cell B3.

Array formula in cell D3:

### 3.1 Explaining formula

#### Step 1 - Search for all digits

The SEARCH function returns a number representing the position of character at which a specific text string is found reading left to right. It is not a case-sensitive search.

SEARCH(*find_text*,*within_text*, [*start_num*])

SEARCH({0; 1; 2; 3; 4; 5; 6; 7; 8; 9}, B3)

becomes

SEARCH({0; 1; 2; 3; 4; 5; 6; 7; 8; 9}, "12?Wi:llia3 3m_s ")

and returns

{#VALUE!; 1; 2; 11; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!}.

#### Step 2 - Replace error values with blanks

The IFERROR function can replace error values with a given value.

IFERROR(*value*, *value_if_error*)

IFERROR(SEARCH({0; 1; 2; 3; 4; 5; 6; 7; 8; 9}, B3), ""))

becomes

IFERROR({#VALUE!; 1; 2; 11; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!})

and returns

{""; 1; 2; 11; ""; ""; ""; ""; ""; ""}.

#### Step 3 - Calculate the largest number in the array

The MAX function returns the largest number in a cell range or array.

MAX(IFERROR(SEARCH({0; 1; 2; 3; 4; 5; 6; 7; 8; 9}, B3), ""))

becomes

MAX({""; 1; 2; 11; ""; ""; ""; ""; ""; ""})

and returns 11.

#### Step 4 - Extract last letter

The MID function returns a substring from a string based on the starting position and the number of characters you want to extract.

MID(*text*, *start_num*, *num_chars*)

MID(B3, MAX(IFERROR(SEARCH({0; 1; 2; 3; 4; 5; 6; 7; 8; 9}, B3), "")), 1)

becomes

MID("12?Wi:llia3 3m_s ", 11, 1)

and returns "s" in cell D3.

### Get Excel *.xlsx file

The array formula in cell C3 extracts the first character from first, middle and last name. The formula works fine […]

This article demonstrates a formula that filters unique distinct single digits from a cell range containing numbers. Cell range B3:B6 […]

This article demonstrates an array formula that returns the table header based on a condition. For example, in cell C8 […]

### 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

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form