How to extract text after a character in Excel

Method 1: TEXTAFTER (Excel 365 and 2024)

TEXTAFTER takes the text and the delimiter, and returns whatever follows the first match. The delimiter can be several characters long, so ": " drops both the colon and the space after it in one go. No TRIM needed.

TEXTAFTER is available in Excel for Microsoft 365, Excel for the web and Excel 2024. In Excel 2021 and older it returns #NAME?, so use Method 2 in files you share with people on older versions.

Method 2: MID and FIND (every version)

FIND returns the position of the character. Add 1 so you start just after it. MID then takes characters from that point on. For the length, LEN(B3) is simply "more than enough": MID stops at the end of the text, so you never need to work out the exact count.

You will also see =RIGHT(B3,LEN(B3)-FIND("-",B3)). It gives the same answer by counting how many characters sit to the right of the dash. MID is easier to adapt when the delimiter is longer than one character: change the +1 to the delimiter's length.

After the second occurrence

Product codes like TSH-RED-L have more than one dash. To get just the size, start the second FIND one character after the first dash. It skips the first match and lands on the second one.

In Excel 365, TEXTAFTER has an instance_num argument that does this directly: =TEXTAFTER(B2,"-",2) returns the text after the second dash.

After the last occurrence (file names from paths)

Paths have a different number of slashes on every row, so "the second slash" is no use. You want the last one. In Excel 365, =TEXTAFTER(D2,"/",-1) does it: a negative instance_num counts from the end.

In older versions, count the slashes with LEN minus LEN of the text with the slashes removed. Then use SUBSTITUTE's instance argument to swap only that last slash for a character that never appears in your data, such as |. FIND locates the |, and MID takes everything after it.

Turn the extracted part into a number

TEXTAFTER, MID and RIGHT always return text, even when the result looks like 48213. Text numbers sit on the left of the cell, SUM skips them, and lookups against real numbers fail. Wrap the formula in VALUE, or add +0 or -- in front.

When the character is missing

Row 5 says "Phone order" with no # at all. FIND returns #VALUE! and TEXTAFTER returns #N/A when the delimiter is not there. Wrap the formula in IFERROR to show a blank or a message instead.

TEXTAFTER also has its own if_not_found argument, the sixth one: =TEXTAFTER(A5,"#",,,,"") returns an empty string without IFERROR, which is safer because it will not hide other errors.