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. 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:
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 line in the middle of the routine:
rCell.Value = Mid(rCell, x + Len(sSeq))