To get text to the left of a specific character in Excel, use the LEFT function combined with FIND or SEARCH. The core formula is =LEFT(A1, FIND("@", A1) - 1), which returns everything before the first “@” in cell A1. This works because FIND locates the character’s position, and LEFT extracts that many characters minus one (to exclude the character itself).
Why You Need Two Functions
Excel has no single “text before” function in older versions, so you combine a position-finder with an extractor. FIND returns the numeric position of a character within a text string. For example, FIND("-", "ABC-123") returns 4 because the hyphen is the fourth character. LEFT then takes that number and pulls that many characters from the start of the string. Subtracting 1 removes the delimiter itself, leaving only the text before it.
If you are using Microsoft 365 or Excel 2024, you have a simpler option: TEXTBEFORE. The formula =TEXTBEFORE(A1, "@") does the entire job in one function. It is not available in Excel 2019 or earlier, so the LEFT/FIND combination remains the most broadly compatible approach.
Step-by-Step: Basic Formula
Assume your data is in column A, starting at A1, and you want everything before the first comma. In B1, enter:
=LEFT(A1, FIND(",", A1) - 1)
If A1 contains “John Smith, Manager”, FIND(",", A1) returns 11, so LEFT extracts the first 10 characters: “John Smith”. Drag the formula down for other rows. If a cell has no comma, FIND returns a #VALUE! error, so you may want to wrap it in IFERROR:
=IFERROR(LEFT(A1, FIND(",", A1) - 1), A1)
That version returns the whole cell if no delimiter exists, which is often more practical for messy data.
FIND vs. SEARCH: Case Sensitivity and Wildcards
FIND is case-sensitive and does not allow wildcards. SEARCH is case-insensitive and allows wildcards like * and ?. For most delimiters like commas or hyphens, either works. Choose FIND when the case matters, for example extracting before “ID” but not before “id”. Choose SEARCH when you need to find a pattern, such as the first digit: =LEFT(A1, SEARCH("0", A1) - 1) finds the first zero. If you need the first space, both work identically.
Also note that FIND and SEARCH only locate the first occurrence. If your string has multiple delimiters and you want the text before the last one, you need a different approach using SUBSTITUTE and LEN to find the position of the last instance.
Handling Multiple Delimiters and Last Occurrence
To extract text before the last occurrence of a character, you must first find its position. The formula to get the position of the last slash in A1 is:
=FIND("@", SUBSTITUTE(A1, "/", "@", LEN(A1) - LEN(SUBSTITUTE(A1, "/", ""))))
This works by counting how many slashes exist (LEN(A1) - LEN(SUBSTITUTE(A1, "/", ""))), then replacing the last one with a unique placeholder (@) and finding that. Wrap that whole expression inside LEFT minus 1. For example, with “a/b/c” in A1, it returns “a/b”. This is advanced but very useful for file paths or URLs.
For the first occurrence, you can also use LEFT with SEARCH if you need case-insensitive matching. The syntax is identical: =LEFT(A1, SEARCH("x", A1) - 1).
Common Errors and How to Fix Them
- #VALUE! error: The delimiter is not found. Use
IFERRORto return a default value or the original text. - #NAME? error: You misspelled a function name or used
TEXTBEFOREin an older Excel version. Check the function spelling and your Excel version. - Wrong result when delimiter is at the start: If A1 starts with the delimiter,
FINDreturns 1, soLEFTextracts 0 characters, giving an empty string. That is correct behavior. - Trailing spaces: The result may include spaces before the delimiter. Use
TRIMaround the whole formula:=TRIM(LEFT(A1, FIND(",", A1) - 1)). - Multiple spaces or tabs:
FINDtreats each space as a character. If your delimiter is a space but there are double spaces, you may get unexpected results. Clean data first withTRIMorCLEAN.
Alternatives: TEXTBEFORE, Flash Fill, and Text to Columns
If you are on Microsoft 365, TEXTBEFORE is the cleanest solution. The syntax is =TEXTBEFORE(text, delimiter, [instance_num]). For example, =TEXTBEFORE(A1, ",") returns everything before the first comma. To get before the second comma, use =TEXTBEFORE(A1, ",", 2). This function also handles missing delimiters gracefully if you set the fourth argument to return the original text.
For one-off tasks, Flash Fill (Data > Flash Fill, or Ctrl+E on Windows, Cmd+E on Mac) can extract text without formulas. Type the expected result in the adjacent column, press Ctrl+E, and Excel fills the rest based on the pattern. It is fast but not dynamic — if your source data changes, the filled values do not update.
Text to Columns (Data > Text to Columns) splits data at a delimiter into separate columns. It is useful when you want the result in a new column rather than a formula. However, it overwrites the original data unless you copy it first, and it is a one-time operation.
Frequently Asked Questions
How do I extract text before a space in Excel? Use =LEFT(A1, FIND(" ", A1) - 1). If the cell starts with a space, it returns an empty string. For names like “John Smith”, this gives “John”.
What if the character appears multiple times? By default, FIND returns the first occurrence. To get before the second or third, use FIND with a start number: =LEFT(A1, FIND(",", A1, FIND(",", A1)+1) - 1) gets before the second comma. For the last occurrence, use the SUBSTITUTE trick described above.
Does this work in Google Sheets? Yes, LEFT, FIND, and SEARCH work identically in Google Sheets. Google Sheets also has SPLIT and REGEXEXTRACT for more complex patterns, but the basic formula is the same.
How do I get text after the character instead? Use RIGHT with LEN: =RIGHT(A1, LEN(A1) - FIND(",", A1)). This returns everything after the first comma. For text between two characters, combine MID and FIND.
Why does my formula return a number instead of text? If the extracted part looks like a number, Excel may convert it. To keep it as text, use =LEFT(A1, FIND("@", A1) - 1)&"" or format the cell as Text before entering the formula.
Can I use a wildcard to find the first digit? Yes, with SEARCH. For example, =LEFT(A1, SEARCH("0", A1) - 1) finds the first zero. To find any digit, you need an array formula or MIN with FIND for each digit, which is more complex. A simpler approach is =LEFT(A1, MATCH(TRUE, ISNUMBER(VALUE(MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1))), 0) - 1) entered with Ctrl+Shift+Enter in older Excel, but that is rarely needed.
Related
- Excel Customer Information Template Guide
- Excel Conditional Formatting Per Row: How To
- Excel Conditional Formatting for Duplicates: How-To
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Split Function Renal Scan: Purpose and ResultsA split function renal scan is a nuclear medicine test that estimates how much each kidney contributes to…
- Excel Add Quotes Around Text With Formulas and TipsYes. In Excel for Microsoft 365 on Windows, enter =""""&A2&"""" in another cell to place straight double quotation…
- COUNTIF Excel: How to Reference Cells in CriteriaTo make COUNTIF use a cell as its criterion, put the cell reference in the second argument: with…
- How to Call a Function in Snowflake (Step-by-Step)Calling a function in Snowflake means executing a built-in or user-defined function (UDF) to return a value, transform…
- Extract Day from Date in Excel: Formula and StepsTo extract the day of the month from an Excel date, put the date in A2 and enter…
- Extract Initials from Names in Excel with FormulasTo extract the first letter of every word in a name, enter a formula in the next column…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.