Find the Position of Text in a Cell (FIND and SEARCH)

To find text in a cell in Excel, use FIND or SEARCH: they tell you where the text is, as a character number. That position is what lets LEFT and MID split names, pull order numbers out of sentences, and check whether a cell contains a word. This guide is for anyone cleaning up exported data or preparing for an Excel test, and it explains when to use FIND and when SEARCH is the better choice.

How to find text in a cell with FIND#

FIND returns the position of the first match, counting from 1 at the left. The syntax:

=FIND(find_text, within_text, [start_num])

The examples use these customer names:

In B2, type:

=FIND(" ",A2)

The result is 5: D-a-n-a is 4 characters, so the space is the 5th. Fill down (Windows: Ctrl+D; Mac: ⌘D) and the position changes with each name. Watch the order: what you look for comes first. =FIND(A2," ") looks for the whole name inside a space and returns #VALUE!.

How to use the position to split text#

On its own a position is just a number, but LEFT and MID turn it into text. The first name is everything before the space, so take one character fewer than the space's position:

=LEFT(A2,FIND(" ",A2)-1)

For Dana Whitfield, FIND gives 5, minus 1 is 4, and LEFT takes Dana. Without the -1 you'd get "Dana " with a trailing space.

The last name starts one character after the space. MID needs a start and a length; use LEN(A2) as the length, because MID simply stops at the end of the text:

=MID(A2,FIND(" ",A2)+1,LEN(A2))

This returns Whitfield. The same pattern splits emails at the @ or codes at a dash. For the basics of these functions, see LEFT, RIGHT and MID explained.

When to use SEARCH instead of FIND#

Use SEARCH when capitals shouldn't matter. FIND is case-sensitive, so it treats R and r as different letters. SEARCH ignores case. Take these support tickets in column F:

=SEARCH("refund",F2) finds the word in every row, whatever the capitals. =FIND("refund",F3) returns #VALUE!, because F3 has REFUND in capitals.

The other difference is wildcards. SEARCH treats ? as any one character and * as any number of characters, so =SEARCH("ord?r",F2) finds "order". FIND treats them as normal characters, so =FIND("?",F4) returns 19, the position of the question mark. To search for a real ? with SEARCH, put a tilde before it: "~?". For a side-by-side comparison, read FIND vs SEARCH in Excel.

How to pull a number out of a sentence#

Find a marker next to the number, then use MID. Each ticket has a 5-digit order number right after the #:

=MID(F2,SEARCH("#",F2)+1,5)

SEARCH finds the # at position 18 in F2, MID starts one character later and takes 5 characters: 48213. The result is text; wrap it in VALUE if you need a real number.

How to check if a cell contains text#

FIND and SEARCH return a number when they find the text and #VALUE! when they don't. ISNUMBER turns that into TRUE or FALSE:

=ISNUMBER(SEARCH("refund",F2))

This returns TRUE for all four tickets. Put it inside IF to label rows:

=IF(ISNUMBER(SEARCH("refund",F2)),"Refund","Other")

Use FIND instead of SEARCH when the capitals matter, such as checking for a product code like "PRO" but not "pro". To count cells that contain a word, you don't need SEARCH at all: =COUNTIF(F2:F5,"*refund*") returns 4.

How to find the second or last occurrence#

Use start_num to skip past the first match. For "Mary Ann Smith", the first space is at 5. To find the second space, start looking at 6:

=FIND(" ",A2,FIND(" ",A2)+1)

The result is 9. The inner FIND finds the first space; adding 1 tells the outer FIND to start just after it.

Finding the last occurrence with FIND takes a longer formula. In Microsoft 365, TEXTAFTER does it directly: =TEXTAFTER(A2," ",-1) returns Smith. See TEXTBEFORE and TEXTAFTER for more.

Why does FIND return #VALUE!?#

FIND and SEARCH return #VALUE! when the text isn't in the cell, and sometimes for less obvious reasons:

1. Wrong capitals with FIND. Switch to SEARCH.
2. Arguments swapped. The text to find comes first, the cell second.
3. Extra or odd spaces. Two spaces between names, or a non-breaking space from a web page, won't match " " the way you expect. Clean the data first; see how to clean messy data.
4. start_num too big. A start position past the end of the text returns #VALUE!.

When a missing match is normal, show something friendlier with IFERROR: =IFERROR(FIND("@",A2),"No @").

If you just want to find a cell on the sheet, not a position inside a cell, use Find (Windows: Ctrl+F; Mac: ⌘F). To join text back together after splitting it, see how to combine text in Excel. The LEFT function guide covers the function you'll pair with FIND most often.

Practise it#

Practise FIND and SEARCH in a live spreadsheet. You'll find the space in each name, split first and last names with LEFT and MID, use SEARCH on tickets with mixed capitals, and pull out order numbers.