Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and replace for cells with certain number of characters.
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and replace for cells with certain number of characters.
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 "JRC" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and replace for cells with certain number of characters.
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). -- Rick (MVP - Excel) "JRC" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and replace for cells with certain number of characters.
On Dec 26, 9:31*am, "Rick Rothstein"
wrote: 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). -- Rick (MVP - Excel) 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and replace for cells with certain number of characters.
On Dec 26, 8:42*am, "Otto Moehrbach"
wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and replace for cells with certain number of characters.
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) "JRC" wrote in message ... On Dec 26, 9:31 am, "Rick Rothstein" wrote: 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). -- Rick (MVP - Excel) 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and replace for cells with certain number of characters.
On Dec 28, 1:32*am, "Rick Rothstein"
wrote: 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) Hi, Rick. I am running Microsoft Office 2004 for Mac (OS X 10.5.8). Joe |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and replace for cells with certain number of characters.
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 "JRC" wrote in message ... On Dec 26, 8:42 am, "Otto Moehrbach" wrote: 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and replace for cells with certain number of characters.
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). 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. -- Rick (MVP - Excel) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FIND and REPLACE characters needed | New Users to Excel | |||
FIND and REPLACE characters needed | Excel Worksheet Functions | |||
Find cells containing a specified number of characters | Excel Discussion (Misc queries) | |||
How do I find replace special characters? | Excel Discussion (Misc queries) | |||
Find and replace unusual characters ... | Excel Discussion (Misc queries) |