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.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
When you are entering information in a worksheet, it sure would be handy to have a way to "mask" the information being ...
Discover MoreEach time you take some action in Excel, the action is saved in an "undo stack" so that the action can be undone, if ...
Discover MoreData validation can be used to create lists of choices for entry into a particular cell. Using the techniques in this tip ...
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