SheetSense

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.

fx
=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

  1. 01FIND("-",A2) returns the position of the first hyphen. In HDW-4821-B that is 4.
  2. 02Subtracting 1 gives 3, the number of characters before the hyphen.
  3. 03LEFT(A2,3) returns the first three characters, HDW.
  4. 04IFERROR returns the original text when there is no hyphen, because FIND fails with #VALUE! in that case.

The same logic in four tools

Excel
=IFERROR(LEFT(A2,FIND("-",A2)-1),A2)
Google Sheets
=IFERROR(LEFT(A2,FIND("-",A2)-1),A2)
SQL
-- PostgreSQL. Returns the whole value when there is no hyphen.
SELECT SPLIT_PART(sku, '-', 1) AS category FROM inventory;
Python (pandas)
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.