Find and replace for cells with certain number of characters.

[JRC]

Hi, everyone.


I am working on a rather large document so searching it manually isn't
possible. I need to find all cells in this document (a text document
imported as a tab-delimited file) that have more than 64 characters.
Although I have found reference to the LEN function in Excel's help
files I would prefer to work with Find and Replace command as it finds
one occurrence and stops there only moving to the next if you press
the Next button.

Is there a way to do this with Find and Replace ?

TIA,


Joe

 

[Otto Moehrbach]

You say you want to use Find & Replace and you say you want to find every
cell that has over 64 characters. The two don't have anything to do with
each other. The following macro will look at every cell in the used range
and will display a message box showing the cell address of each such cell,
one at a time. The message box will stay there until you click the OK
button, then it will display the cell address of the next over-64 cell, and
so on. HTH Otto
Sub Find64()
Dim i As Range
For Each i In ActiveSheet.UsedRange
If Len(i) > 64 Then _
MsgBox i.Address(0, 0)
Next i
End Sub

 

[Rick Rothstein]

If you are looking for the cells with 65 or more characters in them, use
this in the "Find what" field of the Find dialog box...

?????????????????????????????????????????????????????????????????*

There are 65 question marks followed by an asterisk (question marks stand
for single character while the asterisk stands for zero or more characters).

 

[JRC]

If you are looking for the cells with 65 or more characters in them, use
this in the "Findwhat" field of theFinddialog box...

?????????????????????????????????????????????????????????????????*

There are 65 question marks followed by an asterisk (question marks stand
for single character while the asterisk stands for zero or more characters).

Hi, Rick.


Thanks for your reply.

I just tried what you suggested but it didn't work. I tried the same
with 15 question marks and it worked so I am thinking that it might
have something to do with the number of question marks. What do you
think ?


Joe

 

[JRC]

You say you want to useFind&Replaceand you say you want tofindevery
cell that has over 64 characters.  The two don't have anything to do with
each other.  The following macro will look at every cell in the used range
and will display a message box showing the cell address of each such cell,
one at a time.  The message box will stay there until you click the OK
button, then it will display the cell address of the next over-64 cell, and
so on.    HTH  Otto
Sub Find64()
    Dim i As Range
    For Each i In ActiveSheet.UsedRange
        If Len(i) > 64 Then _
            MsgBox i.Address(0, 0)
    Next i
End Sub

Hi, Otto.


Thanks for your reply and help with the subroutine.

I just tried the subroutine and it worked fine. The boxes appear on
the display and display the cell location for the occurrence. However,
one thing I would like to be able to do is to have the cursor move to
the actual cell and display the cell on the center of the screen so
that I have the chance to either modify the content or format it.

Is it possible to do it with a modified version of this subroutine
similar to the way the Find and Replace window works ?


Joe

 

[Rick Rothstein]

It worked when I tried it on my copy of Excel before I posted it as an
answer to your question. I just tried it again and it is still working. So I
am not sure what to tell you. What version of Excel are you using?

--
Rick (MVP - Excel)

If you are looking for the cells with 65 or more characters in them, use
this in the "Findwhat" field of theFinddialog box...

?????????????????????????????????????????????????????????????????*

There are 65 question marks followed by an asterisk (question marks stand
for single character while the asterisk stands for zero or more
characters).

Hi, Rick.


Thanks for your reply.

I just tried what you suggested but it didn't work. I tried the same
with 15 question marks and it worked so I am thinking that it might
have something to do with the number of question marks. What do you
think ?


Joe

 

[JRC]

It worked when I tried it on my copy of Excel before I posted it as an
answer to your question. I just tried it again and it is still working. So I
am not sure what to tell you. What version of Excel are you using?

Hi, Rick.

I am running Microsoft Office 2004 for Mac (OS X 10.5.8).

Joe

 

[Otto Moehrbach]

JRC
This macro will select the cell and place it in the top left corner.
Placing it in the middle of the screen is not always possible and that will
result in the cell appearing in different locations on the screen. This
way, the cell will always be in the top left corner. HTH Otto
Sub Find64()
Dim i As Range
For Each i In ActiveSheet.UsedRange
If Len(i) > 64 Then
i.Select
Application.Goto ActiveCell, Scroll:=True
MsgBox i.Address(0, 0)
End If
Next i
End Sub

 

[Rick Rothstein]

It worked when I tried it on my copy of Excel before I posted it

Hi, Rick.

I am running Microsoft Office 2004 for Mac (OS X 10.5.8).

I'm running Excel on a PC. I don't have a Mac available to me, so I can't
test it out, but I'm willing to bet using a Mac has something to do with why
it is not working for you. You might try posting your question again, but
this time mention that you are using a Mac in the Subject of your post so
that volunteers with a Mac can (hopefully) help you find a solution.