How do I find cells that being with a blank?
 

::Hi,
I have a spreadsheet with circa 11,000 rows, approx. 6 columns.
Some of the data MAY contain values that begin with a blank space,
e.g. the cell may look like (ignore quotes)

\" Fred\"

when it should be

\"Fred\"

Is there an easy way to change the cell colour to Red for all cells in
a spreadsheet where the first character is blank ?

Thanks in advance for your kind help.

Matt::


--
bvinternet
------------------------------------------------------------------------
bvinternet's Profile: http://www.excelforum.com/member.php...o&userid=25525
View this thread: http://www.excelforum.com/showthread...hreadid=389620

Select the cells, starting with A1, and goto FormatConditional Formatting
and change the condition to Formula Is, add a formula of
=LEFT(A1,1)=" "
click Format, select Pattern, and pick the red.
OK out.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"bvinternet" wrote
in message ...

::Hi,
I have a spreadsheet with circa 11,000 rows, approx. 6 columns.
Some of the data MAY contain values that begin with a blank space,
e.g. the cell may look like (ignore quotes)

\" Fred\"

when it should be

\"Fred\"

Is there an easy way to change the cell colour to Red for all cells in
a spreadsheet where the first character is blank ?

Thanks in advance for your kind help.

Matt::


--
bvinternet
------------------------------------------------------------------------
bvinternet's Profile:
http://www.excelforum.com/member.php...o&userid=25525
View this thread: http://www.excelforum.com/showthread...hreadid=389620

One way:

1) select the range you want evaluated (say A1:A100)
2) go to menu FormatConditional Formatting
3) In condition 1 choose 'Formula is' from the 1st dropdown
4) put the following formula into the formula box: =LEFT(A1)=" "
(where A1 is the reference to cell in the selected range that is active,
usually the first one)
5) presss the Format button and select your prefered formats
6) press OK,OK

Another way:

1) Insert new column
2) Write the following formula and copy it down:
=TRIM(A1)
3) Copy the column with the new values
4) Select the original column and Paste Special Values.

Regards,
KL


"bvinternet" wrote
in message ...

::Hi,
I have a spreadsheet with circa 11,000 rows, approx. 6 columns.
Some of the data MAY contain values that begin with a blank space,
e.g. the cell may look like (ignore quotes)

\" Fred\"

when it should be

\"Fred\"

Is there an easy way to change the cell colour to Red for all cells in
a spreadsheet where the first character is blank ?

Thanks in advance for your kind help.

Matt::


--
bvinternet
------------------------------------------------------------------------
bvinternet's Profile:
http://www.excelforum.com/member.php...o&userid=25525
View this thread: http://www.excelforum.com/showthread...hreadid=389620

opps! the first formula should be =LEFT(A1,1)=" "

Apologies. KL


"KL" wrote in message
...
One way:

1) select the range you want evaluated (say A1:A100)
2) go to menu FormatConditional Formatting
3) In condition 1 choose 'Formula is' from the 1st dropdown
4) put the following formula into the formula box: =LEFT(A1)=" "
(where A1 is the reference to cell in the selected range that is active,
usually the first one)
5) presss the Format button and select your prefered formats
6) press OK,OK

Another way:

1) Insert new column
2) Write the following formula and copy it down:
=TRIM(A1)
3) Copy the column with the new values
4) Select the original column and Paste Special Values.

Regards,
KL


"bvinternet"
wrote in message
...

::Hi,
I have a spreadsheet with circa 11,000 rows, approx. 6 columns.
Some of the data MAY contain values that begin with a blank space,
e.g. the cell may look like (ignore quotes)

\" Fred\"

when it should be

\"Fred\"

Is there an easy way to change the cell colour to Red for all cells in
a spreadsheet where the first character is blank ?

Thanks in advance for your kind help.

Matt::


--
bvinternet
------------------------------------------------------------------------
bvinternet's Profile:
http://www.excelforum.com/member.php...o&userid=25525
View this thread:
http://www.excelforum.com/showthread...hreadid=389620