Hi all,
I've provided a simplified example of what I'm trying to achieve. I would like to delete all rows that contain a "q" in the 2nd position of Column C. So in this example, that would be Columns 6,9,11, and 13. Any suggestions how to best achieve this? Thanks in advance!
If there are only two characters in each cell, you could Filter on "ends with q" and delete the filtered/visible rows. If there are more characters, you could add a helper column. Use MID to test for q. Filter on the helper column. Again, delete the filtered/visible rows.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Instead of mid, you can use find =Find ("q", A2) for example will return the position in the string in cell A2 where it finds "q" or #N/A! if it doesn't. SO = ISNUMBER(FIND("q",A2)) will be true if A2 contains "q" and false if not.Originally Posted by TMS
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
Not sure what the advantage would be, but the OP did say:The FIND would pick up a "q" in any position, not just the second, unless I am mistaken.that contain a "q" in the 2nd position of Column C.
Ah then = FIND("q",A2) = 2.
I have to admit that I missed the requirement for second position. Mid would also work in that case.
You'll need IFERROR to cater for the not present scenario.
Yup! Why use two formulas when one will do? So Mid wins this one.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks