# Delete contents of cells that have fewer than 'n' characters.

#### Ozhatch

##### Board Regular
I have a list of words. I want to use find-and-replace to eliminate certain letters from the list, so that I'm left with only those which contain a predetermined number of characters.

Example: I've eliminated the letter 'e' from this list: loavs; locals; locris; lodgs; logics; longus; lonrs; loris; lovrs; lowrs. Now I want to delete all strings that have fewer than 6 characters.

This is a variable problem, 'n' characters being the variable.

Please would someone supply a solution?

Thanks.

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### JoeMo

##### MrExcel MVP
This assumes your list is in col A, starting in A2.
Code:
``````Sub LessThanN()
Const N As Long = 6 'set target length here
'assumes list is in col A and begins in A2
Dim V As Variant, i As Long
V = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value
For i = 1 To UBound(V, 1)
If Len(Trim(V(i, 1))) < N Then V(i, 1) = ""
Next i
Application.ScreenUpdating = False
Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value = V
Application.ScreenUpdating = True
End Sub``````

Last edited:

#### footoo

##### Well-known Member
Here's another way. First select the range to be processed :
Code:
``````Sub LessThanN()
Dim n As Variant
n = Application.InputBox(prompt:="Enter a number", Type:=1)
If TypeName(n) = "Boolean" Then Exit Sub
Selection = Evaluate(Replace("if(len(#)<" & Val(n) & ","""",#)", "#", Selection.Address))
End Sub``````

#### Ozhatch

##### Board Regular
Thank you both for your suggestions. I'd like to try both of them, however I'm not familiar with the use of code in Excel, and I'd really appreciate it if you could give me step-by-step instructions on how and where to enter the code.

~~~~~~~~~~~~~~~~~~~~~~~
If there are no stupid questions, then what kind of questions do stupid people ask? Do they get smart just in time to ask questions?
– Scott Adams US cartoonist (1957 - )

#### JoeMo

##### MrExcel MVP
To install standard module code:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the code from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Press Alt+F8 keys to run the code
7. Make sure you have enabled macros whenever you open the file or the code will not run.

#### Ozhatch

##### Board Regular
Thanks again, JoeMo! I'll print that, and keep it close.

Replies
3
Views
286
Replies
1
Views
98
Replies
0
Views
103
Replies
10
Views
230
Replies
8
Views
267