Deleting Everything Up to a Character Sequence

Steven has a worksheet that has lots of text in it. In the cells in column A he wants to delete everything that may occur before a given sequence of characters, such as everything before =XX=. There may be multiple instances of these characters in each cell, but Steven only wants to delete everything before the first occurrence.

One way to do this is to use a formula. If you are using Excel 2021 or the version included with Microsoft 365, then you can use the following formula:

=TEXTAFTER(A1,"=XX=")

If you have some cells that may not include your delimiters, then you need to compensate for them. This version of the formula uses the IFERROR function to handle what to do if the delimiter is not there:

=IFERROR(TEXTAFTER(A1,"=XX="),A1)

If you are using an older version of Excel, then you need to rely on a formula that is a bit more complex. For instance, the following formula will evaluate whatever is in cell A1 and simply return everything up to the =XX= characters. If the characters are not found in the cell, then the entire cell is returned:

=RIGHT(A1,IF(ISERROR(FIND("=XX=",A1,1)),
LEN(A1),LEN(A1)-FIND("=XX=",A1,1)+1))

If you want, instead, to not return the first occurrence of =XX=, all you need to do is change the +1 near the end of the formula to -3.

If you prefer a macro-based solution you could use a routine like the following. It examines all the cells that are currently selected and then deletes everything before the =XX= sequence.

Sub DeleteToSequence()
    Dim rCell As Range
    Dim sSeq As String
    Dim x As Long

    sSeq = "=XX="
    For Each rCell In Selection
        x = InStr(rCell.Value, sSeq)
        If x > 0 Then
            rCell.Value = Mid(rCell, x)
        End If
    Next

    Set rCell = Nothing
End Sub

You should be aware that this macro can cause some errors, particularly when what you are searching for begins with an equal sign (as in =XX=). When a string beginning with an equal sign is stuffed back into the cell, you'll get a #NAME? error because Excel tries to parse the cell as if it contains a formula.

If you want to delete everything up through the character sequence, use this If structure in the middle of the routine instead of the one that is in the code above:

If x > 0 Then
    rCell.Value = Mid(rCell, x + Len(sSeq))
End If

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8446) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Deleting Everything Up to a Character Sequence.