Written by Allen Wyatt (last updated October 9, 2021)
This tip applies to Excel 97, 2000, 2002, and 2003
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:
=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 line in the middle of the routine:
rCell.Value = Mid(rCell, x + Len(sSeq))
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7696) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Deleting Everything Up to a Character Sequence.
Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!
Do you need to know when a workbook was last changed? There are a couple of ways you can go about keeping track of the ...
Discover MoreAt the very heart of editing is the ability to move and copy cells in a worksheet. Understanding the differences between ...
Discover MoreThe Clipboard is essential to move or copy information from one place in Excel to another. If you get an error when you ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments