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

Ozhatch

Board Regular
Joined
Oct 31, 2011
Messages
65
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Some videos you may like

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
Joined
May 26, 2009
Messages
17,323
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Sep 21, 2016
Messages
3,055
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Oct 31, 2011
Messages
65
Office Version
  1. 2007
Platform
  1. Windows
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.


:confused:

~~~~~~~~~~~~~~~~~~~~~~~
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
Joined
May 26, 2009
Messages
17,323
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Oct 31, 2011
Messages
65
Office Version
  1. 2007
Platform
  1. Windows
Thanks again, JoeMo! I'll print that, and keep it close. (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,112,817
Messages
5,542,666
Members
410,567
Latest member
SCraig123
Top