# Extract specific word based on position in cell value

#### Table of Contents

## 1. Extract first word in cell value

The formula in cell C3 grabs the first word in B3 using a blank as the delimiting character.

The SEARCH function looks for a "space" character in cell B3 and returns 7, if you want to use a different delimiting character change the first argument in the SEARCH function.

We don't need the space character so we subtract the number returned from the SEARCH function with 1.

The LEFT function then extractsÂ the first word in cell B3 using the calculated number.

### 1.1 Explaining formula

#### Step 1 - Find string in value

The SEARCH function returns a number representing the position of character at which a specific text string is found reading left to right.

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

SEARCH(" ", B3)-1

becomes 7-1 and equals 6.

#### Step 2 - Extract string based on position

The LEFT function extracts a given number of characters always starting from the left.

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

LEFT(B3, SEARCH(" ", B3)-1)

becomes

LEFT(B3, 6)

becomes

LEFT("Martin Smith", 6)

and returns "Martin" in cell C3.

## 2. Extract the first word in cell - return warning if not found

The following formula warns if the delimiting character is not found.

The SEARCH function returns #VALUE error if the delimiting character is not found. The COUNT function counts how many numbers are in a cell or cell range, it also ignores errors that come in handy in this case.

The COUNT function returns 0 (zero) in cell B3 and the IF function interprets that as a FALSE. The third argument in the IF function is returned "Wrong delimiting character?".

### 2.1 Explaining formula

#### Step 1 - Find position of character

The SEARCH function returns a number representing the position of character at which a specific text string is found reading left to right.

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

SEARCH(" ",B3)-1

becomes

SEARCH(" ","AA-BB")-1

becomes

#N/A! -1 and returns #N/A!.

#### Step 2 - Extract string from value

The LEFT function extracts a given number of characters always starting from the left.

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

LEFT(B3,SEARCH(" ",B3)-1)

becomes

LEFT(B3,#N/A!)

and returns #N/A!

#### Step 3 - Count numbers

The COUNT function counts all numerical values in an argument, it allows you to have up to 255 arguments.

COUNT(SEARCH(" ",B3))

becomes

COUNT(#N/A!)

and returns 0 (zero).

#### Step 4 - Show warning if the character is not found

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(*logical_test*, [*value_if_true*], [*value_if_false*])

IF(COUNT(SEARCH(" ",B3)), LEFT(B3,SEARCH(" ",B3)-1),"Wrong delimiting character?")

becomes

IF(0, LEFT(B3,SEARCH(" ",B3)-1),"Wrong delimiting character?")

and returns "Wrong delimiting character?" in cell C3.

### Get Excel *.xlsxÂ file

Extract first word in cell.xlsx

## 3. Extract n-th word in the cell value

Formula in cell D3:

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 in the formula if the cell value contains a different string delimiting character.

### Explaining the formula in cell C3

#### Step 1 - Delete leading and trailing spaces

The TRIM function deletes all blanks (space characters) except single blanks between strings in a cell value.

TRIM(B3) returnsÂ "Martin Williams".

#### Step 2 - Replace remaining spaces

The SUBSTITUTE function replaces each space character in the cell value to 200 space characters. The REPT function repeats the space character 200 times.

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

becomes

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

and returnsÂ "MartinÂ Â Â Â Â Â Williams". (I have shortened the string for obvious reasons.)

#### Step 3 - Extract values

The MID function returns characters from the middle of a text string based on a start character and a number representing the length.

MID(SUBSTITUTE(TRIM(B3)," ",REPT(" ",200)), (C3-1)*200+1, 200)

becomes

MID("MartinÂ Â Â Â Â Â Williams", (C3-1)*200+1, 200)

becomes

MID("MartinÂ Â Â Â Â Â Williams", 201, 200)

and returns "Â Â Â Â Williams".

#### Step 4 - Once again delete leading and trailing spaces

Lastly, the TRIM function removes all blanks (space characters) except single blanks between strings in a cell value.

TRIM(MID(SUBSTITUTE(TRIM(B3)," ",REPT(" ",200)), (C3-1)*200+1, 200))

becomes

TRIM("Â Â Â Â Williams")

and returns "Williams" in cell D3.

## 4. Extract n-th string in cell value - Excel 365

Excel 365 formula in cell D3:

### 4.1 Explaining formula

#### Step 1 - Split strings in value

The TEXTSPLIT function splits a string into an array based on delimiting values.

Function syntax: TEXTSPLIT(Input_Text, col_delimiter, [row_delimiter], [Ignore_Empty])

TEXTSPLIT(B3,," ",TRUE)

becomes

TEXTSPLIT("Martin Williams",," ",TRUE)

and returns

{"Martin"; "Williams"}.

#### Step 2 - Get k-th string in array

The INDEX function returns a value or reference from a cell range or array, you specify which value based on a row and column number.

Function syntax: INDEX(array, [row_num], [column_num])

INDEX(TEXTSPLIT(B3,," ",TRUE),C3)

becomes

INDEX({"Martin"; "Williams"},2)

and returns "Williams".

### 4.2 Get Excel *.xlsx file

Extract k-th word in cell.xlsx

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

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

## 6. 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:

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

### 6.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)

returns {10; #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!}, "")

and returns

{10; ""; ...; ""}.

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

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

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

### 7.2 Get Excel *.xlsx file

Extract last word in cell.xlsx

Recommended reading:

- LEFT until character
- Extract first word in cell value
- Extract k-th word in cell value
- Extract last word in cell value
- Extract first three digits in value
- Extract numbers in cell value
- Extract unique digits

### Extract category

What's on this page How to extract numbers from a cell value - Excel 2016 Sort and return unique distinct […]

I this article I will show you how to get numerical values from a cell range manually and using an […]

### Excel categories

### 2 Responses to “Extract specific word based on position in cell value”

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

Good article. Often, I need to select up to a delimiter character or the whole cell contents, whichever is applicable.

Suppose cell A2 = "ABC|DEF", cell A3 = "Words". And, in this case, my delimiter is the vertical bar or pipe character "|".

`=left(A2,search("|",A2 & "|")-1)`

For cell A2, I append a vertical bar, so what I'm searching is actually the string "ABC|DEF|". The search will still find the first vertical bar (position 4) and then subtract 1 for the left function, producing "ABC".

If we used cell A3 instead of A2, appending the vertical bar to the string "Words" would give us "Words|". The position returned by the search function is 6, and subtracting 1 from this (now 5) gives us the length of the string, so the left function gets the original string.

Whether you use this approach or not is largely dependent upon what you're trying to produce. This covers most of my use cases, and when I discovered it, it simplified lots of my coding (SQL and Excel for sure).

Thank you Jack for your comment.

Very useful if a cell does not contain the specified delimiting character at all.

=LEFT(B3,SEARCH(" ",B3&" ")-1)