List first letter upper case

Column B in a spreadsheet has a single word in each cell. Some words
begin with an upper case letter, others with a lower case letter. Can
someone please tell me a simple way to extract a list of those rows
where the word in column B begins with an upper case letter?

Enter this in, say, F2:
=EXACT(LEFT(B2,1),UPPER(LEFT(B2,1)))
Then select column B, use Data/Filter/Advanced filter. Enter F1:F2 in the
Criteria range, then click OK.

Bob Umlas
Excel MVP


"Bob Frolek" <[email protected]> wrote in message
news:[email protected]...
> Column B in a spreadsheet has a single word in each cell. Some words
> begin with an upper case letter, others with a lower case letter. Can
> someone please tell me a simple way to extract a list of those rows
> where the word in column B begins with an upper case letter?
>

Try...

C1:

=SUM(IF($B$1:$B$100<>"",(CODE(LEFT($B$1:$B$100))>=65)*(CODE(LEFT($B$1:$B$
100))<=90)))

....confirmed with CONTROL+SHIFT+ENTER.

D1, copied down:

=IF(ROWS($D$1:D1)<=$C$1,INDEX($B$1:$B$100,SMALL(IF(($B$1:$B$100<>""),IF((
CODE(LEFT($B$1:$B$100))>=65)*(CODE(LEFT($B$1:$B$100))<=90),ROW($B$1:$B$10
0)-ROW($B$1)+1)),ROWS($D$1:D1))),"")

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <[email protected]>,
"Bob Frolek" <[email protected]> wrote:

> Column B in a spreadsheet has a single word in each cell. Some words
> begin with an upper case letter, others with a lower case letter. Can
> someone please tell me a simple way to extract a list of those rows
> where the word in column B begins with an upper case letter?

Tried this one, Bob. All that happened was that the rows with data,
2-9003, disappeared.

Bob Umlas wrote:
> Enter this in, say, F2:
> =EXACT(LEFT(B2,1),UPPER(LEFT(B2,1)))
> Then select column B, use Data/Filter/Advanced filter. Enter F1:F2 in the
> Criteria range, then click OK.
>
> Bob Umlas
> Excel MVP
>
>
> "Bob Frolek" <[email protected]> wrote in message
> news:[email protected]...
> > Column B in a spreadsheet has a single word in each cell. Some words
> > begin with an upper case letter, others with a lower case letter. Can
> > someone please tell me a simple way to extract a list of those rows
> > where the word in column B begins with an upper case letter?
> >

Thanks, Domenic. Tried this and the result in C1 was the number 1.
Copying the formula second formula to D1 and dragging it down, didn't
produce any results.

In article <[email protected]>,
"Bob Frolek" <[email protected]> wrote:

> Thanks, Domenic. Tried this and the result in C1 was the number 1.
> Copying the formula second formula to D1 and dragging it down, didn't
> produce any results.

Make sure that you confirm both formulas with CONTROL+SHIFT+ENTER, not
just ENTER. In other words, after you type the formula, hold both the
CONTROL and SHIFT keys down, then hit the ENTER key. Excel will place
braces around the formula, indicating that you've entered it correctly.

Does this help?

Afraid not, Domenic. The first formula writes the number 1 in C1. The
second formula copies B1 (which begins with a capital letter) to D1,
but not if I change B1 to lower case. But copying the formula in D1
down (or copying both formulas down) gives only blank cells in D2
onwards, irrespective of leading upper or lower case first letter.

Domenic wrote:
> In article <[email protected]>,
> "Bob Frolek" <[email protected]> wrote:
>
> > Thanks, Domenic. Tried this and the result in C1 was the number 1.
> > Copying the formula second formula to D1 and dragging it down, didn't
> > produce any results.
>
> Make sure that you confirm both formulas with CONTROL+SHIFT+ENTER, not
> just ENTER. In other words, after you type the formula, hold both the
> CONTROL and SHIFT keys down, then hit the ENTER key. Excel will place
> braces around the formula, indicating that you've entered it correctly.
>
> Does this help?

Bob

Here is 2 more ways
=PROPER(B1)

=UPPER(LEFT(A1,1))&MID(A1,2,LEN(A1)-1)


The Proper Command

Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.

Syntax

PROPER(text)

Text is text enclosed in quotation marks, a formula that returns text, or a reference to a cell containing the text you want to partially capitalize.

If B1:B10 contains the following...

dog
cat
Horse
cow
Hawk
Eagle
mouse
bird
swan
goat

....the first formula, entered in C1, will return 3 indicating that there
are three cells that begin with a capital letter. The second formula,
entered in D1 and copied down, will return the following...

Horse
Hawk
Eagle

Is this what you're looking for? If so, and you're still having
problems, I can send you a sample. If this isn't what you're looking
for, can you provide more details?

In article <[email protected]>,
"Bob Frolek" <[email protected]> wrote:

> Afraid not, Domenic. The first formula writes the number 1 in C1. The
> second formula copies B1 (which begins with a capital letter) to D1,
> but not if I change B1 to lower case. But copying the formula in D1
> down (or copying both formulas down) gives only blank cells in D2
> onwards, irrespective of leading upper or lower case first letter.

Thanks for sticking with this, Domenic. Apologies for the delay in
replying, caused by a trip out of town.
Yes, this is exactly what I'm looking for.
If I open a fresh worksheet and paste your dog...goat list into B1:B10,
check that there are no leading blanks in each cell, then paste formula
1 into C1, it reports 0. Here is formula 1 for a cross check on whether
I've garbled it:
=SUM(IF($B$1:$B$100<>"",(CODE(LEFT($B$1:$B$100))>=65)*(CODE(LEFT($B$1:$B$100))<=90)))

Domenic wrote:
> If B1:B10 contains the following...
>
> dog
> cat
> Horse
> cow
> Hawk
> Eagle
> mouse
> bird
> swan
> goat
>
> ...the first formula, entered in C1, will return 3 indicating that there
> are three cells that begin with a capital letter. The second formula,
> entered in D1 and copied down, will return the following...
>
> Horse
> Hawk
> Eagle
>
> Is this what you're looking for? If so, and you're still having
> problems, I can send you a sample. If this isn't what you're looking
> for, can you provide more details?
>
> In article <[email protected]>,
> "Bob Frolek" <[email protected]> wrote:
>
> > Afraid not, Domenic. The first formula writes the number 1 in C1. The
> > second formula copies B1 (which begins with a capital letter) to D1,
> > but not if I change B1 to lower case. But copying the formula in D1
> > down (or copying both formulas down) gives only blank cells in D2
> > onwards, irrespective of leading upper or lower case first letter.