How to extract text before a character in Excel

Method 1: TEXTBEFORE (Excel 365 and 2024)

TEXTBEFORE takes the text and the delimiter, and returns what comes before the first match. It is short, readable and easy to adjust. Fill it down the column and every email gives up its username.

Because it is a formula, the result updates whenever the source cell changes. That makes it a better fit than Text to Columns for lists that keep growing.

Text before a multi-character delimiter

The delimiter can be longer than one character. Use ", " to split a city from its state without leaving a stray comma behind.

Method 2: LEFT and FIND (every version)

FIND returns the position of the character. Subtract 1 so you stop just before it, and LEFT returns that many characters from the start.

Use SEARCH instead of FIND if you need a case-insensitive match for letters. FIND is case-sensitive.

And the text after the character

TEXTAFTER is the mirror image. In older Excel, use =MID(A2,FIND("@",A2)+1,100) to start one character after the delimiter and take the rest.

Before the last or second occurrence

TEXTBEFORE has a third argument, instance_num. =TEXTBEFORE(B3,"-",2) returns "MUG-BLU", the text before the second dash. A negative number counts from the end, so =TEXTBEFORE(B3,"-",-1) also returns everything before the last dash, however many dashes there are.

In any version, you can get the same result by nesting FIND. The inner FIND locates the first dash, and the outer FIND starts searching one character after it, so it lands on the second dash. LEFT then keeps everything before that.

Getting the very last occurrence in older Excel needs a much longer SUBSTITUTE formula. If you can, use TEXTBEFORE or split the column with Data > Text to Columns.