How do I find cells that being with a blank?

[bvinternet]

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

[Bob Phillips]

Select the cells, starting with A1, and goto Format>Conditional 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" <[email protected]> wrote
in message news:[email protected]...
>
> ::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
>

[KL]

One way:

1) select the range you want evaluated (say A1:A100)
2) go to menu Format>Conditional 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" <[email protected]> wrote
in message news:[email protected]...
>
> ::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
>

[KL]

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

Apologies. KL


"KL" <[email protected]> wrote in message
news:%[email protected]...
> One way:
>
> 1) select the range you want evaluated (say A1:A100)
> 2) go to menu Format>Conditional 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" <[email protected]>
> wrote in message
> news:[email protected]...
>>
>> ::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
>>
>
>