Excel XP (2002)

[MarkC]

Like to know if it is possible to do search find, to find a cell with a
certain amount of characters. That is, if a cell has 24 characters can
Excel find it based on having 24 characters? I imported a csv file which
has a column with various text that really goes past 64 characters, I like
to find all cells that exceeds a certain character "amount".

Thanks,

Using Xp Pro Sp2

 

[Dave Peterson]

Maybe you could use:
Edit|Find
what: *????????????????????????*
(24 ?'s surrounded by asterisks (*)).

 

[Dave Peterson]

Or find 24 ?'s and search for just a partial match.

 

[MarkC]

Thanks Dave, it will do, but was hoping for something with a given number,
like *64*. But that will find cells with any combination of 64.
Mark

 

[Gord Dibben]

Maybe in B1 enter

=IF(LEN(A1)=24,"yes","no")

Copy down the column and filter on the answer "yes"


Gord Dibben MS Excel MVP

 

[Dave Peterson]

Maybe you could use a macro:

Option Explicit
Sub testme()
Dim HowMany As Long
Dim FoundCell As Range
Dim resp As Long

HowMany = Application.InputBox(Prompt:="How Many?", Type:=1)

If HowMany < 1 Then
Exit Sub
End If

Set FoundCell = ActiveCell

Do
Set FoundCell = Cells.Find(What:=String(HowMany, "?"), _
After:=FoundCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox "Not Found"
Else
resp = MsgBox(Prompt:="Look again?", _
Title:=FoundCell.Address(0, 0), _
Buttons:=vbYesNo)
If resp = vbNo Then
FoundCell.Select
Exit Do
End If
End If
Loop

End Sub

=====

And if that data is always going in a single column, I'd use Gord's technique.

But I'd put:
=len(a1)
and drag down

Then apply data|filter|autofilter to that column.
Filter to show greater than or equal to anything I want.

But if those long strings can go in any column, I'd use something else--maybe
even conditional formatting.

Select all the cells
with A1 the activecell
format|Cells|conditional formatting
formula is:
=LEN(A1)>=$A$1

Put your number limit in A1 (or any cell you want--but change the Conditional
formatting formula).

 

[Bill Sharpe]

Maybe in B1 enter

=IF(LEN(A1)=24,"yes","no")

Copy down the column and filter on the answer "yes"


Gord Dibben MS Excel MVP

Of course to find all cells that exceed 24 characters you'd modify the
formula to
=IF(LEN(A1)>24,"yes","no")

Bill

 

[MarkC]

Thanks everyone for the help. Will try these out.
Mark