Excel LEN, FIND, LEFT & RIGHT: Text Extraction Formulas

To split text in Excel, combine LEN and FIND with LEFT or RIGHT: use =LEFT(A2,FIND(" ",A2)-1) to return the text before the first space, and =RIGHT(A2,LEN(A2)-FIND(" ",A2)) to return what follows it. These formulas assume A2 contains a two-part value such as Maya Chen; they return Maya and Chen, respectively.

What LEN, FIND, LEFT, and RIGHT Do

These four functions solve different parts of a text-extraction problem. LEN counts the characters in a cell’s value, including spaces and punctuation. FIND locates a specific character or text string and returns its position. LEFT returns characters from the beginning of a value, while RIGHT returns characters from its end.

The functions become especially useful together. FIND tells Excel where a separator is, such as a space, hyphen, comma, or period. LEFT can then return the characters before that position. LEN can calculate how many characters remain after it, and RIGHT can return that remainder. The formulas do not change the original cell; they produce extracted text in the cells where you enter them.

Position counts start at 1, not 0. In Maya Chen, the space is character 5. That is why the first formula subtracts 1: without the subtraction, LEFT would include the space in its result.

Split a Filename at Its Extension

A filename is a clear example of all four functions working together. Suppose A2 contains Q3_Report.xlsx. The period separates the filename from its extension, so FIND locates the period and the other functions return the text on either side.

With Q3_Report.xlsx in A2, this formula finds the period’s position. The result is 10, because the period is the tenth character.

=FIND(".",A2)

With the same value in A2, this formula returns the filename without the extension. It takes one fewer character than the period’s position, so the result is Q3_Report.

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

With Q3_Report.xlsx in A2, this formula returns the text after the period. LEN counts 14 characters, FIND returns 10, and 14 minus 10 leaves 4 characters. The result is xlsx.

=RIGHT(A2,LEN(A2)-FIND(".",A2))

These examples assume there is one period separating the filename and extension. A filename such as archive.backup.zip has more than one period, and FIND returns the position of the first one. That means the LEFT formula returns archive, not archive.backup. Decide which period matters before using the formula on a mixed set of filenames.

How the Formulas Work Together

Read a combined formula from the inside out. In =LEFT(A2,FIND(" ",A2)-1), Excel first evaluates FIND to get the position of the space. It subtracts 1 from that position, then gives the resulting character count to LEFT. For Maya Chen, the calculation is position 5 minus 1, or 4 characters, so LEFT returns Maya.

The right-side formula uses a different calculation. With Maya Chen in A2, LEN returns 9 and FIND returns 5. Subtracting the separator’s position from the total length gives 4, the number of characters after the space. RIGHT therefore returns Chen. This arithmetic works because the separator is a single character.

FIND also accepts an optional starting position. For example, it can search for a later occurrence of a character rather than the first one. This is useful when a value contains repeated separators, but it does not automatically identify the last separator. If your data has a predictable structure, test the result on examples with one separator, several separators, and none.

Extract Text Around a Space or Other Delimiter

A delimiter is the character or text that divides the pieces you want. It might be a space between a first and last name, a hyphen in an ID, or a comma between a city and state. Replace the space in the name formulas with the delimiter you actually have. In Excel formulas, a literal text delimiter must be enclosed in straight double quotation marks.

With an ID such as AB-2047 in A2, this formula returns the characters before the hyphen. FIND returns position 3, so LEFT takes 2 characters and returns AB.

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

With AB-2047 in A2, this formula returns the text after the hyphen. LEN is 7 and FIND is 3, leaving 4 characters for RIGHT, which returns 2047.

=RIGHT(A2,LEN(A2)-FIND("-",A2))

These formulas are most straightforward when each cell has the same pattern: one separator in a consistent place, with a nonempty value on both sides. If a value contains several words, splitting at the first space returns only the first word and all remaining words, not necessarily a first and last name. For instance, splitting Jordan Lee Park at the first space produces Jordan and Lee Park.

Spaces, Missing Delimiters, and Common Errors

Extra spaces can make extracted results look wrong. A value with leading spaces may return an empty result before the first space; repeated spaces may leave unwanted spaces at the start of the second part. One option is to clean a value in a helper column first. With Maya Chen in A2, the following formula returns Maya Chen: TRIM removes leading and trailing ordinary spaces and reduces repeated internal ordinary spaces to one.

=TRIM(A2)

Then, if B2 contains that cleaned result, use the split formulas on B2 instead of A2. For Maya Chen in B2, this formula returns Maya.

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

TRIM handles regular spaces, not every character that may look like whitespace. Data copied from a website or another system can include nonbreaking spaces, which may need separate cleanup. Also, trimming or splitting names is a data-handling choice: a person’s name may not follow a simple first-name/last-name pattern, so preserve the original value if it matters.

FIND returns #VALUE! when it cannot find the requested text. A missing separator therefore makes a combined formula fail. A blank cell can cause the same issue. You can show a chosen fallback instead of the error. With a value in A2, the following formula returns the text before the first space, or an empty string if there is no space. For Maya Chen, the result is Maya; for Maya, the result is blank.

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

Use a meaningful fallback where appropriate, such as a label that signals the row needs review. Do not hide errors automatically if a missing delimiter could indicate a data-quality problem. Another common error occurs when the character count supplied to LEFT or RIGHT is negative; Excel returns #VALUE!. Check the positions and lengths in the source before assuming the formula is wrong.

FIND Versus SEARCH, and When Case Matters

FIND is case-sensitive: searching for A is different from searching for a. It is useful when the capitalization of the text matters or when you want a literal, case-sensitive match. FIND does not support wildcard characters. If the target text is not present with the same capitalization, FIND returns #VALUE!.

SEARCH is the related function to use when the match should ignore capitalization. It also supports wildcard characters, including ? for one character and * for a sequence of characters. For example, with Report.xlsx in A2, this formula finds the period regardless of the capitalization in the surrounding text and returns position 7.

=SEARCH(".",A2)

For a literal period, FIND and SEARCH both locate the period. Wildcards are relevant only when the search text includes wildcard characters. If you are trying to find an actual asterisk or question mark, SEARCH treats those characters as wildcards; choose FIND for a literal, case-sensitive search instead. Neither function changes the text it finds.

Alternatives for Newer Excel Versions

For a simple split at a known delimiter, newer Excel functions can be easier to read. TEXTBEFORE returns text before a delimiter, and TEXTAFTER returns text after it. These functions are available in Microsoft 365 and Excel 2024. They are not available in every older Excel version, so the LEN, FIND, LEFT, and RIGHT approach remains useful when compatibility matters.

With Q3_Report.xlsx in A2, this formula returns Q3_Report, the text before the period.

=TEXTBEFORE(A2,".")

With the same value in A2, this formula returns xlsx, the text after the period.

=TEXTAFTER(A2,".")

TEXTBEFORE and TEXTAFTER also have options for choosing a later occurrence of a delimiter and for specifying what to return when the delimiter is absent. Check the function’s available arguments in your Excel version if you need those behaviors. For one-off, simple splits, the Data tab’s Text to Columns tool is another option; it can split a selected column using a chosen delimiter. Unlike a formula, that workflow writes split values into cells, so make sure adjacent cells are empty before using it.

Frequently Asked Questions

What is the formula for text before a space in Excel?
With the text in A2, use =LEFT(A2,FIND(" ",A2)-1). It returns the characters before the first space, so Maya Chen becomes Maya. It returns an error if there is no space unless you handle that case with IFERROR.

How do I extract everything after a character?
With the source value in A2 and a single separator in it, use =RIGHT(A2,LEN(A2)-FIND("-",A2)) to return text after a hyphen. Replace the hyphen with the delimiter in your data. FIND uses the first matching occurrence.

Does FIND count spaces in Excel?
Yes. Spaces count as characters in both LEN and FIND. FIND returns the position of the space, and LEN includes spaces when counting the full cell value. This is why the formula for text after a delimiter subtracts the delimiter’s position from LEN.

Why does FIND return #VALUE!?
FIND returns #VALUE! when the exact, case-sensitive text you asked for is absent. Check the delimiter, capitalization, and source cell, or use SEARCH if the match should ignore capitalization. Use IFERROR only when a fallback is appropriate.

Can I split at the last period instead of the first?
FIND locates the first occurrence, so it will not by itself split a filename at its last period. In Microsoft 365 or Excel 2024, TEXTAFTER can select an occurrence from the end; for older versions, use a formula designed for the data’s pattern or split the text with another suitable workflow.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.