Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Deleting Everything Up to a Character Sequence.
by Allen Wyatt
(last updated December 28, 2013)
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))
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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Need to insert rows in your worksheet? Excel provides a few techniques you can use to do this. Here are some ideas you can ...Discover More
Excel allows you to edit your cell contents in two places. What if you want to limit where editing occurs, so it can only be ...Discover More
Copying information between two instances of Excel is different than copying information between two worksheets opened in a ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.