How to delete text up to a certain character in Excel

In Excel, you quite often need to remove everything in a string before a certain character. It can be a single character (a space, a period, a comma, a colon) or any character from a set, like any digit or any letter.

Depending on the case, the solution can be simple or require rather complex formulas and procedures. Below are all the options I know. If you need to remove a fixed number of characters rather than everything up to a character, see how to remove the first N characters in cells.

Remove all text up to the last character

The easiest is to remove the text in a range up to the last occurrence of a character or text. Use Find and Replace:

• select the range;
• press Ctrl+H or go to Home → Find & Select → Replace;
• in Find what, type an asterisk (*) right before the character, for example “*,”;
• leave Replace with empty and click Replace All.

The procedure replaces all characters up to and including the character with nothing, in other words, deletes them. The asterisk means “any characters”. Why the last occurrence and not the first? Because the asterisk is greedy: it takes as many characters as possible.

To do the same with a formula in Excel 365, use TEXTAFTER with −1, which means the last occurrence:

=TEXTAFTER(A2,",",-1)

Formula to remove everything up to the first space

This formula is universal: it removes text up to the first space or up to the first occurrence of any other character. Still, removing up to the first space is the most popular case, because the space separates the first word from the rest of the text. That’s the same as to remove the first word in a cell.

=REPLACE(A2,1,SEARCH(" ",A2),"")

The formulas for other characters are similar. Up to the first comma:

=REPLACE(A2,1,SEARCH(",",A2),"")

Up to the first period:

=REPLACE(A2,1,SEARCH(".",A2),"")

To remove text up to a sequence of characters, such as a word, add the length of this sequence minus 1. Here, we remove everything up to the order number in “Order No. 4512 shipped”. The text in quotes, “No. ”, is 4 characters long:

=REPLACE(A2,1,SEARCH("No. ",A2)+3,"")

The result is “4512 shipped”. And to keep the character itself, subtract 1 from the position instead: =REPLACE(A2,1,SEARCH(",",A2)-1,"") turns “red, green” into “, green”.

In Excel 365, TEXTAFTER does all of this without calculating positions: =TEXTAFTER(A2,"No. ").

Remove everything up to the first number

You’ve probably guessed it already, but let me explain how the formulas above work:

• the SEARCH function finds the position of the first occurrence of the text pattern, which is always in quotes;
• the REPLACE function takes this position as an argument;
• and replaces the fragment from the first character to this position with nothing (two quotes in a row), that is, deletes it.

So, to remove text up to the first digit, we need the position of the first digit in the string. Since there are 10 digits, not one, a simple formula won’t do. But this array formula will:

{=MIN(IFERROR(SEARCH(CHAR(ROW($48:$57)),A1),""))}

Plugging this expression into the original formula solves the task: we get the text from the first digit on, with everything before it removed.

{=REPLACE(A2,1,MIN(IFERROR(SEARCH(CHAR(ROW($48:$57)),A2),""))-1,"")}

The numbers 48 to 57 are the codes of the digits 0 to 9, see the CHAR function for details. Note the curly braces: don’t type them. In Excel 2019 and earlier, they appear when you confirm the formula with Ctrl+Shift+Enter. In Excel 365 and 2021, just press Enter.

If the string contains no digits, MIN returns 0 and the formula returns an error.

Remove text up to a character with regex

With regular expressions, all these cases are solved by one function with different patterns. REGEXREPLACE is built into Excel 365 and available for free in older versions with !SEMTools:

=REGEXREPLACE(A2,"^[^,]*,\s*","")

This removes everything up to and including the first comma and the spaces after it. And this one removes everything before the first digit:

=REGEXREPLACE(A2,"^\D+(?=\d)","")

The (?=\d) part checks that a digit follows without removing it. Thanks to it, the formula doesn’t touch cells without digits, and unlike the array formula, it doesn’t return an error for them.

Why text isn’t deleted up to the character: common mistakes

• The formula returns #VALUE!. The character is not in the cell, so SEARCH fails. Wrap the formula in IFERROR(…,A2) to keep such cells unchanged.
• Find and Replace removed too much. The asterisk is greedy and goes to the last occurrence. To remove only up to the first occurrence, use a formula.
• The search finds the wrong character. SEARCH treats “?” and “*” as wildcards. To find them literally, put a tilde before them: SEARCH("~?",A2).

Delete text up to a character with !SEMTools

Each of these methods is good in its own way, but they all lack one important thing: speed. Every time you type a lot of characters, fill the formula down and clean up afterwards.

To save time, I added quick procedures to the menu of my !SEMTools add-in for Excel. Now you can remove text before the first or the last occurrence of a specific character or string, including or excluding the character itself, in a moment.

You can either change the data right in place or output the result to the next column: a global checkbox controls this. Here is an example:

Summary: which method to choose

I hope you found what you were looking for on this page and were able to remove the text up to the character you need. Find and Replace with an asterisk is the fastest for the last occurrence, REPLACE + SEARCH for the first one, and TEXTAFTER or regex in Excel 365. For regular work with columns, !SEMTools does it in place in a couple of clicks.

You may also be interested in related operations:

• Remove the last word from a cell
• Extract the first N characters of a cell
• Remove the last N characters in cells