Find blank cells containing spaces

[doctorswamp]

Hi

Need to identify cells that look blank but may contain spaces.

If(Isblank...) doesn't flag them.

Thanks

PS sorry if this is an old chestnut, couldn't see it in faq

 

[Bowers74]

try

=IF(A1=""),dosomething,donothing

Peace!!

Mike

Didn't get the answers that you wanted? Then . . . Click Here

 

[Bowers74]

oops, there should be a space between the quotes:

=IF(A1=" "),dosomething,donothing


Peace!!

Mike

Didn't get the answers that you wanted? Then . . . Click Here

 

[combo]

You can try conditional formatting with 'Formula is' and formula =(FIND(" ",A1)>0) (no $-type reference, selected region starts in A1 - active cell).

combo

 

[Lilliabeth]

=IF(ISBLANK(A1),"","not blank")

This will flag if A1 = a space or if A1 = multiple spaces.

 

[doctorswamp]

Sorry fellas, should have been a bit clearer with my question.

The exercise is part of data validation on a column that may contain reference numbers or blanks.

If there's a number in say cell A3 then a formula in B3 needs to show the number. If A3 is blank then B3 should show "Empty".

The problem was that people sometimes deleted entries in column A by hitting the spacebar, so the formula =If(Isblank(A3)=true,"Empty",A3) was giving a blank, not "Empty".

 

[Lilliabeth]

What if there is text in A3, what do you want displayed?

How about:

=IF(ISNUMBER(A1),A1,"Not a number")

 

[Bowers74]

If this is an excersize in data validation, why not use it:

1. Select all pertaining cells in column A

2. Select Data->Validation...

3. Insert under Allow: Custom

4. Enter under Formula: "=ISNUMBER(A3)"

This way you can eliminate the B column altogether!

If you want to keep the B column Check (based on your formula):

=IF(OR(ISBLANK(A3),A3=" "),"Empty",A3)



Peace!!

Mike

Didn't get the answers that you wanted? Then . . . Click Here

 

[doctorswamp]

Hi Bowers74, Lilliabeth & Combo

Thanks for the combined thoughts which pointed me in the right direction.

What seems to work ok is use in say cell B3

If(LEN(TRIM(A3)>0,A3,"blank")

This takes care of any leading or trailing spaces and shows either the value or text in A3 whether or not it has spaces, or 'blank' if the cell looks empty but might have spaces in it.