To return the text to the left of a character in Excel, use =LEFT(A2,FIND("-",A2)-1), replacing A2 with the cell you want to split and the hyphen with your separator. For example, if A2 contains North-Branch, the formula returns North; it finds the separator and tells LEFT how many characters to keep before it.
The Basic LEFT and FIND Formula
Assume A2 contains North-Branch and you want the part before the hyphen. Enter this formula in another cell:
With the source text in A2, this formula returns North for North-Branch: =LEFT(A2,FIND("-",A2)-1)
LEFT extracts characters starting at the beginning of a text value. FIND locates the position of the first matching character. The minus one is essential: FIND counts the hyphen as a character, but the result should stop immediately before it. In the example, the hyphen is in position 6, so LEFT returns the first 5 characters.
Use a different quoted character to split at another separator. For instance, a space can separate a first name from the rest of a name, while a comma may separate a city from a state. If the separator is stored in a cell, the formula can refer to that cell instead of hard-coding the character.
How the Formula Works
The expression is built from two functions, each doing a distinct job:
- FIND("-",A2) returns the position of the first hyphen in A2. FIND is case-sensitive when searching for letters, although that distinction does not matter for punctuation such as a hyphen.
- LEFT(A2,number_of_characters) returns that many characters from the start of A2.
- The subtraction of 1 excludes the separator itself from the returned text.
These functions do not change the original cell. The formula produces a result in the cell where you enter it, and that result updates if the source text changes. If you need a fixed value rather than a live formula, copy the results and use Paste Special > Values.
FIND returns the first occurrence of the character, not the last. That distinction matters for text containing repeated separators. For example, in East-Region-Office, the basic formula returns East. It does not return East-Region, because FIND stops at the first hyphen.
Use a Separator Stored in Another Cell
A separator in a cell is useful when users may change which character marks the boundary. Assume A2 contains North-Branch and B1 contains a hyphen. The formula below returns North:
With the source text in A2 and the delimiter in B1, use: =LEFT(A2,FIND(B1,A2)-1)
This makes the worksheet easier to maintain: change B1 to a comma, slash, or another single-character delimiter and the formula uses the new value. The delimiter cell must contain a character that appears in the source text. If B1 is blank, FIND locates the empty string at the start of the text, and the formula returns an empty result rather than a useful split.
For a multi-character separator such as a double colon, the same approach works because FIND can search for a text string, not only one character. Assume A2 contains Code::784 and B1 contains ::. With that layout, use =LEFT(A2,FIND(B1,A2)-1); the result is Code. The subtraction still removes the entire separator from the left-side result because FIND gives the position where the matched string begins.
Handle Rows Without the Character
If the separator is absent, FIND returns a #VALUE! error, which makes the basic formula return an error too. Choose how the worksheet should behave when that happens. One common option is to return the original text unchanged.
With source values in A2 and the hyphen as the separator, this formula returns the text before the first hyphen, or the full original value if there is no hyphen: =IFERROR(LEFT(A2,FIND("-",A2)-1),A2)
For example, if A2 is North-Branch, the result is North. If A2 is NorthBranch, the result is NorthBranch. IFERROR is convenient, but it catches any error generated inside its first argument, not just the missing-separator case. If the source data or formula has another problem, returning A2 could conceal it. In a carefully controlled sheet, that may be acceptable; in a complex model, it can be better to inspect unexpected errors rather than suppress them.
Other possible policies include returning a blank or a message. With the source value in A2, this version returns a blank when the hyphen is absent: =IFERROR(LEFT(A2,FIND("-",A2)-1),""). Decide on one policy before filling the formula down, especially if downstream calculations depend on whether a row was actually split.
Choose the First or Last Occurrence
The LEFT and FIND formula is appropriate when the first occurrence marks the boundary. If the required boundary is the last occurrence, a formula designed for the last match is needed. In Microsoft 365 and Excel 2024, TEXTBEFORE has an instance-number argument that can search from the end when given a negative number.
With East-Region-Office in A2, this formula returns East-Region, the text before the last hyphen: =TEXTBEFORE(A2,"-",-1)
TEXTBEFORE is available in Microsoft 365 and Excel 2024. It is often easier to read than a long formula built from older functions. If the value in A2 is East-Office, the last hyphen is also the first, so this example returns East. If there is no match, TEXTBEFORE normally returns #N/A unless you provide its optional not-found argument. That is a different default error from FIND, so account for it when replacing an older formula.
Do not use the last-occurrence version just because a value contains several separators. Decide what the data means: in a path such as Department-Team-Name, the first separator may identify the department, while the last may be the correct split for a different task.
Use TEXTBEFORE as a Simpler Alternative
For many straightforward splits, TEXTBEFORE states the goal more directly: return the text before a delimiter. It is available in Microsoft 365 and Excel 2024, while LEFT and FIND work in a much wider range of Excel versions and are a practical choice for files that must be compatible with older releases.
With North-Branch in A2, this formula returns North: =TEXTBEFORE(A2,"-")
TEXTBEFORE also supports an optional instance number when a delimiter repeats, and an optional value to return when the delimiter is not found. This can make complicated extraction formulas shorter. However, compatibility matters when a workbook is shared: older Excel versions may show a _xlfn. prefix or fail to calculate a function they do not support. If recipients use older software, use LEFT and FIND or confirm the workbook’s target Excel versions before switching.
Another alternative is Data > Text to Columns, which can split a selected column into separate columns without writing a formula. It is useful for a one-time cleanup, but it changes the layout by placing split results in adjacent cells. A formula is usually more suitable when results should update automatically as source values change.
Common Problems and Checks
Most problems with this formula come from the source data not matching the assumed pattern. Before filling it through a large range, test a typical value, a value with no separator, and a value with more than one separator.
- Extra spaces: If the source is North – Branch, the basic formula returns North plus the space immediately before the hyphen. You can remove surrounding spaces by wrapping the result in TRIM. With source text in A2, use
=TRIM(LEFT(A2,FIND("-",A2)-1)); it returns North. TRIM also collapses repeated internal spaces to single spaces, so avoid it if those spaces are meaningful. - Different hyphen characters: A normal hyphen-minus (-), an en dash (–), and an em dash (—) are different characters. If FIND reports an error despite a visible dash, check whether the source uses another dash type and put the exact character in the formula or delimiter cell.
- Separator at the beginning: If A2 starts with a hyphen, the formula returns an empty string because there are zero characters before the first hyphen. That can be correct, but it may also signal malformed input.
- Unexpected first match: FIND uses the first match. Review examples with repeated separators to confirm that the first one is the intended boundary.
- Formula appears as text: If Excel displays the formula itself instead of its result, check whether the cell is formatted as Text. Change the format to General, then re-enter the formula. Also check for an apostrophe before the equals sign.
When filling down, use the fill handle or copy and paste the formula. The reference A2 is relative, so it becomes A3, A4, and so on in subsequent rows. If you store the delimiter in a fixed cell such as B1 and copy the formula down, make that reference absolute as $B$1 so it does not shift. With source text in A2 and the fixed delimiter in B1, use =LEFT(A2,FIND($B$1,A2)-1).
Frequently Asked Questions
What formula returns everything before a space in Excel?
With the text in A2, use =LEFT(A2,FIND(" ",A2)-1). It returns the text before the first space. If some entries do not contain a space, wrap the expression in IFERROR and choose whether to return the original value or a blank.
How do I return text before a character without an error?
With source text in A2 and a hyphen separator, use =IFERROR(LEFT(A2,FIND("-",A2)-1),A2) to return the original text when no hyphen exists. Replace A2 in the final IFERROR argument with an empty string, written as "", if you prefer a blank result.
Does LEFT and FIND return text before the first or last character?
It returns text before the first matching character because FIND locates the first occurrence. In Microsoft 365 or Excel 2024, TEXTBEFORE with an instance number of -1 can return the text before the last occurrence instead.
Can the separator be more than one character?
Yes. FIND can search for a string such as ::, and LEFT can return the characters before it. Make sure the formula uses the exact separator, including any spaces.
Why does my formula return #VALUE!?
The most common cause is that the exact character or string in FIND does not occur in the source cell. Check for a different dash, an extra space, or inconsistent data, and decide whether missing separators should produce an error, a blank, or the original text.
Related
- Excel: Find Names in Two Worksheets
- Excel Find the Minimum Value in a Range With Criteria
- Excel Find the Minimum Date With Criteria
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- ROUNDUP Excel Cell Reference: 10 Useful Formula PatternsWith 12.31 in A2, enter =ROUNDUP(A2,0) to return 13; the formula reads the number from a cell and…
- Excel Formula to Return a Blank When No ValueYes. In Excel for Microsoft 365 on Windows, use an IF formula that returns an empty text string…
- SUMIF Excel Examples With Two Criteria to Match RowsTo total values that match two conditions in Excel, use SUMIFS rather than SUMIF. With regions in A2:A8,…
- Excel VLOOKUP Shortcut Keys: What Actually WorksExcel has no dedicated keyboard shortcut that inserts or runs VLOOKUP by itself. The quickest keyboard approach is…
- Excel F4: How to Lock Cell References in FormulasTo lock a cell reference in an Excel formula, edit the formula, place the cursor in or select…
- Excel Freeze Panes: Keep Top Row and Left Column VisibleTo freeze the top row and left column together in Excel for Microsoft 365 on Windows, select cell…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.