How to delete text up to a certain character in Excel

In Excel, it’s quite common to remove everything in a string before a certain character. That character might be a space, a dot, a comma, a colon—or even something from a group, like any number character or letter.

Depending on your case, the solution can be simple or may require more advanced formulas and techniques.

This article walks through all the options I know. Let’s go!

Remove all text up to the last character

The easiest method for deleting text up to the last occurrence of a symbol or substring is by using Excel’s built-in Find and Replace tool:

• Select your range
• Press Ctrl + H or go to Home → Find & Select → Replace
• In the “Find what” field, type an asterisk (*) followed by the target character
• Leave “Replace with” blank and click “Replace All”

This replaces everything up to and including the character with nothing—effectively deleting it. The asterisk acts as a wildcard for “any characters”.

Formula to remove everything up to the first space

The formula below is universal—you can adapt it to remove everything up to the first space or any other character:

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

To remove up to the first comma:

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

To remove up to the first period:

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

If you want to remove everything up to a certain word or substring, include the length of that substring minus one. In the example below, the string “d. “ has a length of 3:

=REPLACE(A2, 1, SEARCH("d. ", A2) + 2, "") 

Remove everything up to the first number

Here’s how the earlier formulas work:

1. SEARCH returns the position of the first instance of the target character (enclosed in quotes).
2. REPLACE takes that position and replaces all characters from the beginning of the string up to that position with an empty string.

But numbers are trickier—we’re looking for one of 10 characters (0–9), not just one.

Here’s an array formula that finds the position of the first number character:

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

To fully remove everything before the first number character, plug that into REPLACE like this:

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

The numbers 48 to 57 represent the ASCII codes for 0 through 9. You can learn more about this in the CHAR function reference.

Note: You don’t need to type the curly braces. Just enter the formula and press Ctrl + Shift + Enter to confirm it as an array formula.

This formula will return an error if no number characters are found.

Do it faster with !SEMTools

All of these techniques work, but they take time. You often need to type long formulas or click through multiple steps.

That’s why I added one-click procedures to my Excel add-in, !SEMTools.

You can now instantly remove text before the first or last occurrence of any character or string, with or without including that character in the deletion.

And you can choose whether to overwrite the original values or output the result in the next column—just toggle the global checkbox.

Here’s a demo:

This post is also available in RU.