To return everything to the left of a character in Excel, combine LEFT with FIND: with the text in A2 and a hyphen as the separator, enter =LEFT(A2,FIND("-",A2)-1). For example, if A2 contains INV-2048, the result is INV. FIND locates the separator, and subtracting one tells LEFT to stop just before it.
How LEFT and FIND work together
LEFT returns a specified number of characters from the start of a text value. FIND returns the position of one text string inside another. Used together, they let you specify a stopping point by its content rather than guessing how many characters to take.
The subtraction matters because FIND counts the separator itself. In INV-2048, the hyphen is character 4. LEFT(A2,4) would include the hyphen, while LEFT(A2,3) returns only INV. FIND(A2) is not the right argument order: the text to search for comes first, followed by the cell or text to search within.
For the example formula, the cell layout is A2 containing INV-2048, with a hyphen between the prefix and the number. Enter =LEFT(A2,FIND("-",A2)-1) in another cell, such as B2. FIND returns 4, the calculation becomes 4-1, and LEFT returns the first three characters: INV.
Basic syntax and choosing a delimiter
The general pattern is LEFT(text, number_of_characters), where the second argument is calculated as FIND(delimiter, text)-1. The delimiter can be a hyphen, space, comma, slash, colon, or a longer text string. Put text values used as delimiters in double quotation marks; refer to a cell containing the delimiter without quotation marks.
For example, with a product code in A2 and a colon separating its prefix from the rest, use =LEFT(A2,FIND(":",A2)-1). If A2 contains CAT:0042, the result is CAT. With a name in A2 written as Morgan Lee and a space separating the first and last names, =LEFT(A2,FIND(" ",A2)-1) returns Morgan. In both cases, FIND selects the first matching delimiter from the left.
Check that the delimiter in the formula matches the actual data. A normal hyphen (-), en dash (–), and em dash (—) are different characters, even though they may look similar. A formula searching for a hyphen will not find an en dash in the cell. If a delimiter is stored in B1, the formula can refer to that cell instead; with the source text in A2 and the delimiter in B1, enter =LEFT(A2,FIND(B1,A2)-1).
Examples for common text-cleanup tasks
The same pattern is useful whenever records place a consistent separator after the part you need. Here are typical cases:
- Extract a department code: With HR/West in A2 and a slash marking the boundary,
=LEFT(A2,FIND("/",A2)-1)returns HR. - Keep the text before a comma: With Seattle, WA in A2,
=LEFT(A2,FIND(",",A2)-1)returns Seattle. - Extract a date prefix: With 2026_11_03 in A2,
=LEFT(A2,FIND("_",A2)-1)returns 2026. - Take a label before a colon: With Status: Open in A2,
=LEFT(A2,FIND(":",A2)-1)returns Status.
Each example assumes the separator occurs in the source cell. If you are applying the formula down a list, enter it beside the first record and fill it down. Excel adjusts A2 to A3, A4, and so on when the reference is relative. Double-clicking the fill handle at the bottom-right corner of the formula cell commonly fills the formula alongside adjacent data; you can also drag it down.
Spaces, punctuation, and exact matches
Excel returns characters exactly as they appear, including spaces immediately before the delimiter. If A2 contains North – 17, the part before the hyphen includes a trailing space. That may be harmless for display, but it can cause problems when the result is used in a lookup or compared with another cell.
To remove extra spaces around the extracted text, use TRIM. With North – 17 in A2 and a hyphen separating the label, enter =TRIM(LEFT(A2,FIND("-",A2)-1)). The LEFT-and-FIND portion returns North followed by a space, and TRIM removes the leading and trailing spaces from the result. TRIM also reduces repeated spaces between words to single spaces, so use it only when that cleanup is wanted.
FIND is case-sensitive, although that usually does not affect punctuation delimiters such as a hyphen or slash. If you are locating a letter and case should not matter, SEARCH is the case-insensitive alternative. For instance, if A2 contains AbcXyz and you want the text before either x or X, use SEARCH rather than FIND. With AbcXyz in A2, =LEFT(A2,SEARCH("x",A2)-1) returns Abc. If case must match exactly, keep FIND.
Handling rows without the character
FIND returns an error when the delimiter is absent. That means a basic LEFT-and-FIND formula also returns an error for a row that does not contain the expected character. This is often useful while checking inconsistent source data, because it reveals which rows need attention. For a polished report, you may prefer to return the original text or a blank instead.
With source text in A2 and a hyphen expected, use =IFERROR(LEFT(A2,FIND("-",A2)-1),A2) to return the text before the first hyphen when present and the full original value when it is not. For example, INV-2048 becomes INV, while a value such as INV2048 remains INV2048. IFERROR catches errors from the calculation and substitutes the specified fallback.
If rows without a hyphen should instead produce an empty cell, use this version. With the text in A2, enter =IFERROR(LEFT(A2,FIND("-",A2)-1),""). A blank result can make a summary cleaner, but it hides the fact that a separator was missing. When data quality matters, consider keeping the error or adding a separate check so incomplete records are not overlooked.
First occurrence, repeated characters, and longer separators
FIND returns the position of the first matching occurrence. If A2 contains west-east-north, the basic hyphen formula returns west. It does not automatically select the last hyphen or split at every hyphen. This distinction matters with dates, addresses, filenames, and codes that may contain the same character more than once.
If the rule is to split at the last occurrence, Microsoft 365 and Excel 2024 offer TEXTBEFORE with an instance number of -1. With west-east-north in A2, enter =TEXTBEFORE(A2,"-",-1) to return west-east. TEXTBEFORE is available in Microsoft 365 and Excel 2024, but not in every older Excel edition. For a formula intended to work in older versions, you may need a more involved construction or a different data-splitting approach.
The delimiter can also be more than one character. FIND searches for the complete string, so a formula can stop before a sequence such as a space followed by a vertical bar. With A2 containing Item 7 | Ready and the separator consisting of space, vertical bar, and space, use =LEFT(A2,FIND(" | ",A2)-1). The result is Item 7. Matching the full separator avoids accidentally stopping at a similar character elsewhere in the text.
LEFT and FIND compared with other options
For a fixed-width code, LEFT alone is simpler because the number of characters is known. With ABC123 in A2 and a three-character prefix, =LEFT(A2,3) returns ABC. But it will still take the first three characters if the format changes; it does not inspect a delimiter.
TEXTBEFORE is a more readable option when available. With INV-2048 in A2, =TEXTBEFORE(A2,"-") returns INV without needing to calculate a character count or subtract one. It can also specify which occurrence to use. Use LEFT with FIND when compatibility with older Excel versions matters, or when you need to combine the position calculation with other logic. Use TEXTBEFORE for clarity in Microsoft 365 or Excel 2024 when its behavior fits the task.
Text to Columns is another choice for a one-time split of a selected range. In Excel for Microsoft 365 on Windows, select the cells and choose Data > Text to Columns, then select Delimited and specify the separator. This creates separate columns rather than a formula result that updates when the source changes. On Mac, the Text to Columns command is also on the Data tab, though the dialog appearance can differ. For a repeatable calculation that updates with source text, a formula is generally more convenient.
Entering, filling, and checking the formula
In Excel for Microsoft 365 on Windows, select the output cell, type the formula beginning with an equals sign, and press Enter. To apply it to more rows, copy or fill the formula down. If your Excel regional settings use semicolons as formula argument separators, Excel may require semicolons instead of commas; this is a regional setting difference, not a different LEFT or FIND method.
Before relying on the output, check a few records manually. Confirm the separator appears where expected, that it does not occur earlier inside the text, and that spaces in the result are acceptable. Also test a row with no delimiter. If some records contain a different dash or separator, fix the source values or account for those cases explicitly rather than assuming the formula has extracted the intended part.
When the result appears unexpected, click the formula cell and inspect the formula bar. A common mistake is forgetting -1, which includes the delimiter in the result. Another is searching for a character that looks similar but is not identical. A third is using the wrong source-cell reference after copying the formula. These checks usually identify the cause quickly.
Frequently Asked Questions
How do I get text before a space in Excel?
With the text in A2 and a space between the first word and the rest, enter =LEFT(A2,FIND(" ",A2)-1). This returns the text before the first space. If the cell starts with a space, the result will be blank, so check the source formatting if that is unexpected.
Why does my LEFT and FIND formula return #VALUE!?
FIND returns #VALUE! when it cannot locate the character you asked it to find. Check whether the cell contains the exact delimiter, including the correct dash type or spacing. If missing separators are normal, wrap the extraction in IFERROR and choose whether to return the original text, a blank, or another useful value.
How can I return everything before the second hyphen?
The basic pattern finds the first hyphen only. In Microsoft 365 or Excel 2024, TEXTBEFORE can target an occurrence by number; for example, with A2 containing north-east-zone, =TEXTBEFORE(A2,"-",2) returns north. This option is not available in some older Excel versions, where a different formula or a data-splitting tool may be needed.
Does FIND distinguish uppercase and lowercase?
Yes. FIND is case-sensitive when searching for letters. SEARCH is the alternative when letter case should be ignored. For punctuation separators, the distinction normally makes no difference, but the exact character still has to match.
Can Excel extract text before a character that appears more than once?
Yes, but decide whether you mean the first or last occurrence. LEFT with FIND uses the first occurrence. In Microsoft 365 or Excel 2024, TEXTBEFORE can select a later occurrence by number, including the last occurrence with a negative instance number. Check the result against examples where the separator repeats.
Related
- Excel Meal Prep Template: Plan Meals, Shop Smarter
- Excel Template for Quotation Comparison: Build One
- Excel Task List Template: Set Up and Customize Yours
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Number Format to Round Values to Millions EasilyTo display Excel values in millions without changing the stored numbers, apply the custom number format #,##0.0,,"M". The…
- Power Query Sort: How to Sort and Keep Rows in OrderTo sort data in Power Query in Excel for Microsoft 365 on Windows, open the query, select the…
- Budget vs Actual Excel Formula: Examples and SetupIf budget is in B2 and actual spending or revenue is in C2, the basic budget-versus-actual formula is…
- COUNTIF Function Examples in ExcelIn Excel for Microsoft 365, COUNTIF counts cells in a range that satisfy one condition. With statuses in…
- Compare Two Lists in Excel to Find Unique ValuesTo find values in one Excel list that are missing from another, use FILTER with XMATCH; wrap the…
- Excel Shortcut to Pull Down a Formula: Fill Down FastIn Excel for Microsoft 365 on Windows, select the formula cell and the cells below where you want…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.