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 (8446) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel 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 select cells in a worksheet, there is a good chance that if you glance at the Task Bar, you'll see some ...
Discover MoreExcel makes it easy to transpose your data so that rows become columns and columns rows. It doesn't have a built-in ...
Discover MoreExcel makes it easy to copy and paste a range of cells. Easy, that is, unless the range isn't contiguous. If you have a ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2018-12-04 18:37:14
Peter Atherton
Gary
Use one or the other line, not both. The following shows possible results
The active cell shows an error because it mixes Text & numbers in A8. The lines used are show in row 6.
(see Figure 1 below)
Figure 1.
2018-12-03 09:39:25
Gary
Thank you for this tip. There is one part that I don't quite understand. In the very last part of the tip you mention adding "rCell.Value = Mid(rCell, x + Len(sSeq))" in the middle of the routine. Do you mean that it replaces something that is already there, or just add this as an extra line? And where exactly do I add it- can you be a bit more specific?
Thank you!
Gary
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2021 Sharon Parq Associates, Inc.
Comments