## Extract first word in cell

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

SEARCH(" ", "Martin Smith")-1

becomes

7-1 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.

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

### 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 […]

### 2 Responses to “Extract first word in cell”

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