Highlighting cell with space bar (blank) character

rlarson · May 17th, 2002, 03:14 PM

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 · May 17th, 2002, 03:18 PM

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 · May 17th, 2002, 03:24 PM

Quote:

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 · May 17th, 2002, 03:25 PM

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

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 · May 17th, 2002, 03:36 PM

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

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

HTH

rlarson · May 17th, 2002, 03:37 PM

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 · May 17th, 2002, 03:37 PM

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

rlarson · May 17th, 2002, 03:40 PM

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

=FIND(" ",A1,1)

Nimrod · May 17th, 2002, 03:43 PM

Cheers

May 17th, 2002, 03:43 PM	#9
Nimrod MrExcel MVP Join Date: Apr 2002 Location: Vancouver BC , Canada Posts: 6,259	Cheers __________________ <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 17th, 2002, 03:14 PM	#1
rlarson New Member Join Date: Feb 2002 Location: Los Angeles, CA Posts: 28	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

May 17th, 2002, 03:18 PM	#2
PHMayfield New Member Join Date: Apr 2002 Location: Phoenix, AZ, USA Posts: 29	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 ]

May 17th, 2002, 03:25 PM	#4
Nimrod MrExcel MVP Join Date: Apr 2002 Location: Vancouver BC , Canada Posts: 6,259	Here's what worked in a cell for me... you shoud be able to modify it for a conditional format as well [i]=IF(AND(COUNTBLANK(A1)=0,LEN(A1)>0),"true","False")[i] 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 ]

May 17th, 2002, 03:36 PM	#5
lenze MrExcel MVP Join Date: Feb 2002 Location: Helena, MT Posts: 13,690	Just playing around and this seems to work for text, but not for numbers. =NOT(ISERROR(SEARCH(" ",A4))) HTH

May 17th, 2002, 03:37 PM	#6
rlarson New Member Join Date: Feb 2002 Location: Los Angeles, CA Posts: 28	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" ??

May 17th, 2002, 03:40 PM	#8
rlarson New Member Join Date: Feb 2002 Location: Los Angeles, CA Posts: 28	Nimrod, Thank you very much. It worked great!! =FIND(" ",A1,1)