Cleaning Cells

[jomili]

I have access to a pivot table. From the pivot I can get to the source data. In the source data, I'm trying to use a formula to put a value in a cell. The current form of the formula I'm trying to use is this:

Please Login or Register  to view this content.

The purpose of the formula is to check if AL, AH and AI are blank, and if so, put "DE" in the formula cell. The problem is there's "Something" in those cells. My formula WAS

Please Login or Register  to view this content.

. That formual doesn't work unless I click into AL, AH, and AI and hit delete. I've already removed any blank spaces in the cell, and I did LEN to assure myself there were no characters in the cell. Still the formula didn't work unless I deleted the contents of each empty cell.

So, I tried with TRIM, and with CLEAN. With those two, the formula wouldn't work, period. So, something is in those cells, and I need to know how to clean them out, because there are about 40K lines to clean.

Any help is greatly appreciated.

Thanks,
John

[ChemistB]

A likely suspect is double quotes or a null string (a string of 0 length). Instead of ISBLANK, in another cell, try = AL2="" and see if it comes up TRUE.

[jomili]

Yes, it's true. So, knowing that, how do I clean the cells?

[ChemistB]

Hmm, you could modify your formulas to replace (ISBLANK(AL2) with AL2=""

Or, if all your data is formulas or number,
Select your entire data area, hit F5 > Special > Check "Constants" and "Text"
That should select all of your "". You can delete all at once. Does that work for you?

Or, I could call over a VBA person and they probably have a macro for it.

[jomili]

Hmm, selecting the whole worksheet, hitting F5 > Special > Checking "Constants" and "Text" highlihgts all my data except numbers. I don't think I want to delete that.

I can't use a macro, because the actions I do are going to be replicated by tons of Budget Analysts (I'm working out the process for them) and I don't want to have to teach them how to create or even run a macro.

If I replace (ISBLANK(AL2) with AL2="" I'll only be checking for those cells that are empty and have the problem. Not all cells in the columns have the problem. Is there a way to select ONLY those values?

[Colin Legg]

Does this formula work for you?

Please Login or Register  to view this content.

[jomili]

Colin,

Thanks. Now I have three formulas that will do the trick. They are:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.