Formula to search all cells in a workbook for characters longer than 6

Hi,

I'm looking for a formula that will check all cells in a workbook for characters longer than 6 and then if longer than 6 I need it to replace the data in that cell with "n/a". I know it has to be a combination of using a named range that encompasses an entire workbook, and then a formula like: If Cell in (named range) is > Len(6) then "N/A". Something like that, but not sure. Just trying to avoid ###### in a cell which does not look good, and I don't want to increase column width. Also cannot use VBA for this.

Thanks in advance.

Thanks for the quick reply. I guess I could setup a named range that just covers each work sheet and then use an if statement to check each cell, but was hoping I could avoiding having to do that, because I have 15 worksheets to cover. Yes most of the cells with have formulas, I was thinking I would just wrap this formula around the existing ones.

Any resulting data or formula results that shows ###### for the cell. Which would only occur if the data or characters in that cell are larger than the cell can display.

Is there a function or conditional format that can change a cell showing ###### and substitute "n/a" in that cell? Just don't want to show ######. I know I can just widen the column width but that also squeezes other cells and increases the width of the sheet.

This just seems 'wrong', but is probably the least expensive method:

1. Select the entire worksheet by clicking the 'Select All' button, or pressing CTRL-A
2. Click Conditional Formatting and enter the formula =LEN(A1)>6, where 6 is your max display length
3. Set the Format to Font Color = White, or whatever your sheet's background colour is.

In the example below, cells B2:H13 and B15:H26 have identical contents, but I have applied the above formatting only to B15:H26.

hide_hash_cells.jpg

Whatabout using the in-built find and replace?

I.e. press CTRL + H, Find what: type ??????? (7 question marks), Replace with: n/a, click Options>>, Look in: Values, Replace All.

Edit: Or if you're looking for ###s, just do the above, but instead of searching for questions marks you search ######.

Good catch on the asterisk and back-up copy, Tony.

With regards to formulas, I've accounted for that by using Look In: Values.