To return the text before a character in Excel, combine LEFT with FIND: if A2 contains INV-2048, enter =LEFT(A2,FIND("-",A2)-1) to get INV. In Microsoft 365, TEXTBEFORE can do the same job more directly; the best choice depends on your Excel version and how you want to handle missing delimiters.
What “text left of a character” means
In a text-extraction question, “left of a character” usually means every character that appears before the first occurrence of a separator, without returning the separator itself. For example, taking text left of the hyphen in INV-2048 returns INV, while taking text left of the slash in North/West returns North. Excel does not need to treat the separator as a special symbol: it can be a hyphen, comma, colon, space, slash, or a longer string.
The distinction between “before” and “including” matters. If you want the separator included, the formula needs a different character count. And if a cell has several matching characters, a basic formula normally finds the first one. Deciding what should happen in those cases before copying a formula down a long column can prevent subtle data-cleaning mistakes.
The classic LEFT and FIND formula
FIND returns the position of one piece of text inside another, counting from 1. LEFT returns a specified number of characters from the start of a text value. Subtracting 1 from the position of the delimiter tells LEFT to stop just before it.
With INV-2048 in A2 and a hyphen as the delimiter, the hyphen is at position 4. The formula therefore asks LEFT for 3 characters and returns INV:
With the text value in A2, use this formula to return everything before the first hyphen:
=LEFT(A2,FIND("-",A2)-1)
For a comma-separated value such as Seattle,WA, change the character inside FIND to a comma. For a space-delimited value, use a space between the quotation marks. The text being searched and the delimiter both need to be enclosed in straight double quotation marks when typed directly into a formula.
Examples with common delimiters
The same pattern works with many ordinary data formats. The key is to specify the actual separator in the cell, rather than assuming Excel will infer it. For instance, a hyphen and an en dash look similar but are different characters; a formula searching for one will not find the other.
- Order codes: With
ORD-5821in A2, text before the hyphen isORD. - Email-like text: With
alex@example.comin A2, text before the at sign isalex. - File names: With
report.xlsxin A2, text before the period isreport. - Location labels: With
East:Warehousein A2, text before the colon isEast.
To retain the delimiter rather than remove it, do not subtract 1 from the position. With INV-2048 in A2, this version returns INV-:
With the same text in A2, include the hyphen by using its position as the character count:
=LEFT(A2,FIND("-",A2))
That small change is useful when splitting a label but keeping its punctuation, such as preserving a trailing colon in a heading or retaining a hyphen that marks a code prefix.
Use TEXTBEFORE for a simpler formula
TEXTBEFORE expresses the task directly: return the text before a specified delimiter. It is available in Microsoft 365 and Excel 2024. With INV-2048 in A2, this formula returns INV:
With the source value in A2, return the part before the first hyphen:
=TEXTBEFORE(A2,"-")
For a basic first-delimiter split, this is easier to read than nesting FIND inside LEFT. TEXTBEFORE also has optional arguments for choosing an occurrence and setting case matching. For example, its optional instance number can select the second occurrence instead of the first. That makes it useful for values such as region-department-code, where the first and second hyphens separate different fields.
If you share a workbook with someone using an older Excel release that does not support TEXTBEFORE, the classic LEFT/FIND combination is generally the more compatible choice. A formula using a newer function may display as an unsupported-function error in an older version, so consider who will open the file before replacing an established formula.
Choose FIND, SEARCH, or TEXTBEFORE
The right function depends on whether matching should distinguish uppercase and lowercase letters and whether the workbook needs to support older Excel versions.
- Use
FINDwhen capitalization must match exactly. It is case-sensitive: searching for lowercasexdoes not match uppercaseX. - Use
SEARCHwhen you want a case-insensitive search. It can also interpret wildcard characters, which can be helpful in some searches but unexpected if the delimiter itself is a question mark or asterisk. - Use
TEXTBEFOREwhen the workbook supports it and you want a function whose name describes the extraction task. Its optional arguments can cover more complex delimiter cases.
For example, if A2 contains NorthX12 and the intended separator might be either uppercase or lowercase X, a case-insensitive search can find it. With that value in A2, the following formula returns North:
With the text in A2, use SEARCH when the delimiter match should ignore capitalization:
=LEFT(A2,SEARCH("x",A2)-1)
With an ordinary hyphen, the case distinction makes no practical difference because a hyphen has no uppercase or lowercase form. For letters used as delimiters, it can be important to choose deliberately rather than changing functions at random when a result looks wrong.
Handle a missing delimiter
The basic LEFT/FIND formula returns a #VALUE! error if the delimiter is absent. That is expected: FIND cannot return a position for a character that is not in the cell. Decide what the output should be for such rows. You might want to return the original text, show a blank, or flag the row for review.
For example, with a hyphen-delimited code in A2, this formula returns the part before the hyphen when one exists and returns the original cell value when there is no hyphen:
With the source text in A2, wrap the extraction in IFERROR to keep unmatched text:
=IFERROR(LEFT(A2,FIND("-",A2)-1),A2)
This is convenient for mixed data, but it can also hide a problem: a missing delimiter may indicate a malformed code, not a value that should be accepted unchanged. If you need to distinguish valid rows from unexpected ones, use an explicit status column or a deliberate fallback such as a message, rather than silently treating every error as normal. IFERROR also catches errors beyond a missing delimiter, so it may conceal other issues in more complicated formulas.
Common problems and how to fix them
When an extraction formula gives an error or an unexpected result, check the source text and the exact delimiter first. Pasted data can contain punctuation that looks identical on screen but is encoded as a different character. A regular hyphen-minus (-), an en dash, and an em dash are not interchangeable in a search.
- The result is an error: Check whether every source cell contains the delimiter. If not, decide on a fallback such as returning the original value or a blank.
- The result stops too early: The delimiter may occur more than once.
FINDreturns the first match, so inspect the text and decide which occurrence defines the boundary. - The result includes an unwanted space: The source may have a space immediately before the delimiter. A formula that extracts characters faithfully will preserve that space.
- The formula is not accepted: Check quotation marks, parentheses, and argument separators. Some regional settings use semicolons instead of commas in formulas; use the separator expected by that Excel installation.
- The formula works in one file but not another: The receiving Excel version may not support a newer function such as
TEXTBEFORE.
Be cautious about adding TRIM automatically. It removes leading and trailing ordinary spaces and reduces repeated spaces between words to one, which may change meaningful text. It does not remove every whitespace character found in imported data; for example, nonbreaking spaces often require separate cleanup. First identify whether the unwanted character is an ordinary space or another character, then choose a cleanup method that preserves the rest of the value.
Apply the formula to a column
For a list of codes, enter the formula in the first result row, then fill it down alongside the source values. If the source is in A2:A100, place the formula in B2 and copy it through B100. The relative reference A2 changes to A3, A4, and so on as the formula is filled down.
To fill a formula efficiently in Excel for Microsoft 365 on Windows, select the formula cell and drag its fill handle down, or copy the cell and paste into the destination range. If the data is an Excel table, entering the formula in a table column typically fills the calculated-column formula down the table and applies it to new rows. On Mac and Excel for the web, the same formula logic applies; the precise interaction for dragging or filling can vary with the interface, but the formula itself does not change.
Before using extracted text in a lookup, sort, or report, check several representative rows: a normal value, a value with repeated delimiters, and a value missing the delimiter. This quick review helps confirm that the formula matches the real structure of the data rather than only the first example.
Frequently Asked Questions
How do I get everything before a space in Excel?
Use LEFT with FIND, replacing the hyphen with a space in the formula. The search text must be a space between quotation marks. If there may be multiple spaces, confirm whether the first space or another one is the intended boundary.
Does LEFT include the character I search for?
Not when the character count uses FIND(delimiter,text)-1. The subtraction excludes the delimiter. Remove the -1 when you want the result to include the first matching delimiter.
How can I return text before the second delimiter?TEXTBEFORE supports an optional instance number, so in Microsoft 365 or Excel 2024 you can specify the second occurrence. The basic FIND/LEFT pattern finds the first occurrence unless you build additional logic to locate a later one.
Why does my formula return #VALUE?
A common reason is that FIND cannot locate the specified delimiter in that row. It can also happen when the searched character differs from the one in the source, such as a regular hyphen versus a longer dash. Check the cell contents and decide how unmatched values should be handled.
Can I do this without a formula?
For a one-time split, Excel’s Text to Columns feature can separate values using a chosen delimiter. It changes the worksheet data into separate columns rather than keeping a formula that recalculates when the source changes, so choose it when a fixed split is appropriate and keep a copy of the original data if needed.
Related
- Data Validation and Quality Assurance in Excel
- Power Query Remove Leading Zeros Without Losing IDs
- Power Query Reference: What It Does and When to Use It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel IFERROR With IF: How to Handle Formula ErrorsIn Excel, use IFERROR to return a chosen result when a calculation produces an error, and use IF…
- SUMIF Greater Than in Excel: Criteria and ExamplesTo use SUMIF in Excel for values greater than a number, put the comparison in quotation marks as…
- Excel Time Conversion Formula: Hours, Minutes, and MoreIf A2 contains an Excel time, multiply it by 24 to get decimal hours: =A2*24. For decimal minutes,…
- Number Sequence in Excel: Fill Series or Use a FormulaTo create a number sequence in Excel, enter the first two numbers, select both cells, and drag the…
- Why Is HLOOKUP Used in Excel? Uses and Examples ExplainedHLOOKUP is used in Excel to find a value in the top row of a table and return…
- Excel Formula for the Larger of Two NumbersTo return the larger of two numbers in Excel, use the MAX function. With the values in A1…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.