Text before a delimiter with LEFT and FIND
LEFT with FIND returns everything before the first occurrence of a character, which works in every version of Excel and in Google Sheets.
=IFERROR(LEFT(A2,FIND("-",A2)-1),A2)The sheet: Inventory sheet: column A holds SKUs such as HDW-4821-B. You want just the category prefix, HDW, in column B.
How it works, step by step
- 01FIND("-",A2) returns the position of the first hyphen. In HDW-4821-B that is 4.
- 02Subtracting 1 gives 3, the number of characters before the hyphen.
- 03LEFT(A2,3) returns the first three characters, HDW.
- 04IFERROR returns the original text when there is no hyphen, because FIND fails with #VALUE! in that case.
The same logic in four tools
=IFERROR(LEFT(A2,FIND("-",A2)-1),A2)=IFERROR(LEFT(A2,FIND("-",A2)-1),A2)-- PostgreSQL. Returns the whole value when there is no hyphen.
SELECT SPLIT_PART(sku, '-', 1) AS category FROM inventory;import pandas as pd
inventory["category"] = inventory["sku"].str.split("-").str[0]Mistakes that break it
- !FIND is case-sensitive and does not allow wildcards. SEARCH is the case-insensitive version.
- !Without IFERROR, any SKU that has no hyphen returns #VALUE!, which also breaks any total built on the column.
- !This takes text before the first hyphen only. To get the text after the last hyphen you need a different formula, such as TEXTAFTER(A2,"-",-1) in Excel 365.
- !If the cell starts with a hyphen, LEFT(A2,0) returns an empty string rather than an error.
Common questions
- How do I extract text before a character in Excel?
- Use LEFT with FIND: =LEFT(A2,FIND("-",A2)-1). In Excel 365 you can use TEXTBEFORE(A2,"-") instead.
- How do I get the text after the hyphen?
- Use =MID(A2,FIND("-",A2)+1,LEN(A2)) in any version, or TEXTAFTER(A2,"-") in Excel 365.
- What is the pandas equivalent?
- df["sku"].str.split("-").str[0] for the first part, and .str.split("-", n=1).str[1] for everything after the first hyphen.
Got a version of this with your own ranges?
Paste it into the free translator and get the same breakdown for your exact formula. To run it against a real file, upload the CSV and describe the formula you need in a sentence.