How do I delete cells with more than a given number of characters in a column?

Question

Hi all,
I have a little problem in an Excel spreadsheet am working on and I believe someone out there could help me out.
I have a very long column with different words in each cell.
I want to delete all cells with more than 9 characters.
Hope my explanation is succinct.
Thanks in advance.

Answer 1

The only issue in question is whether you have data in the other columns of each word in your 'evaluate' column.



If you do, and you delete selected cells, that will result in a misalignment of data as the cells in the 'evalulate' column will realign with the other columns.



If that is not an issue, then you can use the following macro to do as you wish. This example assumes that column A is the column containing the data. If your column is not column A, change the three "A" references to your column letter, i.e. "B", "F", etc.



Copy the following macro to the clipboard (highlight the entire code, right click inside the highlighted area, and 'Copy'):



Sub EvaluateColumn()

Dim i, LastRow

LastRow = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To LastRow

If Len(Cells(i, "A")) > 9 Then

Cells(i, "A").Delete

End If

Next

End Sub



Press ALT + F11 (Function Key F11)



In the menus at the top of the VBE, select INSERT > MODULE



Paste the macro into the white editing area to the right (right click inside the area and 'Paste').



Still in the VBE, go to Tools > Macros, select the macro, and click 'RUN'.



Close the VBE (red button w/white 'x').





aside: Greg, good to see you delving into the 'dark' side of Excel!

Answer 2

Formula based row deletion is not actually possible, but you could do a two-step process that would count the chars of a cell, and then if it were over a certain number, print out yes, and if not, print out no. You could then filter the column for only where the row says yes, and delete all of the records, then unfilter to leave only the "no" options.



The code would look something like

=IF(LEN(A1)>9, "Y", "N")

Referencing column A that is.

Answer 3

Try this macro:



Sub long_clearer()

Dim r As Range

For Each r In ActiveSheet.UsedRange

If Len(r.Value) > 9 Then

r.Clear

End If

Next

End Sub



Please note that this macro will delete ALL cells on the sheet with more than 9 characters in it.



Press Alt + F11 and from the VBA screen, click Insert >> Module

Paste that code

Close it and from the Developer tab back in Excel, run it.



@Garbo: I knew you'd come up with a better solution. One of these days I'll figure out this VBA thing instead of just editing recorded macros lol

Answer 4

You can use the LEN formula, which shows the Length of a cell.



Assuming that your column is column A:

1. Create a new column next to your column, and use the formula LEN([Cell]) referring to each of your column's cells.

e.g. If the new column is column B, B2 formula should be: "=LEN(A2)", B3 formula should be: "=LEN(A3)" and so on.



2. Sort your data using your new column. Now all the rows should be sorted according to number of characters in the cell.



3. Now that your cells are sorted by length you can select and delete all the rows together.



If you don't want to lose the order your cells were in the beginning, create an extra column before sorting, which is autonumbered (values 1,2,3 etc...) so that when you are done deleting the rows you can sort by that column, and restore the order of your rows.

Answer 5

despite in case you're applying Excel i might pretty propose that in case you export it into get right of entry to as a perfect databse, nonetheless you may desire to no longer at first be as professional in applying this software as Excel you may interior the tip discover it lots less difficult, versatile and quicker to administration, easily with the kinds of searches/queries you attempt to apply on the 2d. Spreadsheets are properly and good yet there's a element the place a database is a lot extra advantageous, and that i think of with what you attempt to do now, you have reached that element in time.