Highlighting cell with space bar (blank) character

[rlarson]

Hi,

Does anyone know how I could use conditional formatting, or a formula, to look for a blank character in a cell, " ", and then highlight the cell or just the blank character?

Any help would be greatly appreciated.

R.Money

 

[PHMayfield]

Do you mean the cell is empty or has a space (" ") in it?

Or do you mean that you want to highlight the cell if it is empty or ANY spaces exist (i.e., would you highlight "Yum Pudding" because of the space or not?
This message was edited by PHMayfield on 2002-05-17 14:19

 

[rlarson]

On 2002-05-17 14:18, PHMayfield wrote:
Do you mean the cell is empty or has a space (" ") in it?

Or do you mean that you want to highlight the cell if it is empty or ANY spaces exist (i.e., would you highlight "Yum Pudding" because of the space or not?
This message was edited by PHMayfield on 2002-05-17 14:19

I would like to highlight the cell because there is an empty space between "Yum Pudding". Or, the highlight could apply to an empty space at the beginning or end of a text/numeric string. " 123abc1" or "cat ".

 

[Nimrod]

Here's what worked in a cell for me... you shoud be able to modify it for a conditional format as well
=IF(AND(COUNTBLANK(A1)=0,LEN(A1)>0),"true","False")

Ooops since the posting you changed your request a little ... this only tests for cells that have "only" blank spaces not a mix of spaces and letters ...
This message was edited by Nimrod on 2002-05-17 14:26

 

[lenze]

Just playing around and this seems to work for text, but not for numbers.

=NOT(ISERROR(SEARCH(" ",A4)))

HTH

 

[rlarson]

You're right. Maybe I should be looking for the "space bar" character in a cell by using a formula that tests for something like:
"If A1 contains " ", then highlight cell A1; if not, then do not highlight" ??

 

[Nimrod]

This in "conditional Formating" worked for me, for finding any blank spaces within a string , in A1.
=FIND(" ",A1,1)

 

[rlarson]

Nimrod,
Thank you very much. It worked great!!

=FIND(" ",A1,1)

 

[Nimrod]

Cheers