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

 

[JE McGimpsey]

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 ?

One way:

Make your search string 65 question marks (?), optionally followed by an
asterisk (*).

This will find all cells with at more than 64 "any" characters.

You might also consider using conditional formatting:

Select the range (with, say, cell A1 the active cell), and choose
Format/Conditional Formatting. Enter the following condition:

Formula is =LEN(A1)>64

and choose a pattern color.

 

[CyberTaz]

This can be virtually impossible if you are not considering all symbols, all
punctuation marks and all spaces as characters to be counted among the 64.

Can you be more specific about the nature of the cell content? Is there any
type of consistency to what the Find would be based on? Do you just want t
*locate* the cells, remove all beyond the 64th character,...?

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[JRC]

This can be virtually impossible if you are not considering all symbols, all
punctuation marks and all spaces as characters to be counted among the 64..

Can you be more specific about the nature of the cell content? Is there any
type of consistency to what the Find would be based on? Do you just want t
*locate* the cells, remove all beyond the 64th character,...?

Regards |:>)
Bob Jones
[MVP] Office:Mac

Hi, CyberTaz.


All cells contain text in the form of alphanumeric characters as in
letters, numbers and spaces. There are in some cases some other
characters such as hyphens but few cells contain them.

I am not trying to remove characters beyond the 64th. What I need to
do is to identify these cells to apply specific formatting such as
background color and perhaps change the contents to uppercase.

After working with Find and Replace I thought the way its window works
would be ideal as I have the chance to stop at every "Find" occurrence
to work on the cell.

Any ideas of what would be the best way to do this ?


Joe

 

[JRC]

One way:

Make your search string 65 question marks (?), optionally followed by an
asterisk (*).

This will find all cells with at more than 64 "any" characters.

You might also consider using conditional formatting:

Select the range (with, say, cell A1 the active cell), and choose
Format/Conditional Formatting. Enter the following condition:

   Formula is    =LEN(A1)>64

and choose a pattern color.

Hi, JE.


Thanks for your reply and suggestions.

I tried what you suggested but it didn't work. My worksheet has nearly
51,000 lines of text with every line containing just one cell. This
cell can be in any row - the row location is based on a certain
hierarchy and the document is formatted using tabs (actually a tab-
delimited text file imported into Excel).

Considering this how would I change the formula you provided so that
the range is right ? Also I noticed that as I tried running the
conditional formatting the formula kept changing as Excel kept placing
the formula between quotes. Am I doing something wrong ?

Thanks again,


Joe

 

[JE McGimpsey]

Thanks for your reply and suggestions.

I tried what you suggested but it didn't work. My worksheet has nearly
51,000 lines of text with every line containing just one cell. This
cell can be in any row - the row location is based on a certain
hierarchy and the document is formatted using tabs (actually a tab-
delimited text file imported into Excel).

Considering this how would I change the formula you provided so that
the range is right ? Also I noticed that as I tried running the
conditional formatting the formula kept changing as Excel kept placing
the formula between quotes. Am I doing something wrong ?

Yes, you're doing something wrong, but from your description, I can't
tell exactly what.

FIrst, when you say "I tried what you suggested", I'm assuming you mean
my second suggestion of Conditional formatting.

I'd also guess that where you write "row" you mean "column".

The range for the formula is right, if, as I suggested, cell A1 is the
active cell. Otherwise, use the address of the active cell. If you use
the active cell address, Excel will change the CF for each cell to refer
to itself, e.g., the CF formula in cell K12435 will read

Formula is =LEN(K12435)>64

For instance, if you select the entire worksheet, and cell B7 is active
(look at the Name Box on the left side of the Formula Bar),then use

Formula is =LEN(B7)>64

Not sure why you're getting the formula between quotes, but if you leave
the range selected, you can choose Format/Conditional Formatting again,
and edit the formula to remove the quotes.

 

[CyberTaz]

A common cause of the quotes is the omission of the = in the formula field.

Regards |:>)
Bob Jones
[MVP] Office:Mac