# List first letter upper case

1. ## 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?  Register To Reply

2. ## Re: List first letter upper case

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" <BobFrolek@hotmail.com> wrote in message
> 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?
>  Register To Reply

3. ## Re: List first letter upper case

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!

"Bob Frolek" <BobFrolek@hotmail.com> 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?  Register To Reply

4. ## Re: List first letter upper case

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" <BobFrolek@hotmail.com> wrote in message
> > 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?
> >  Register To Reply

5. ## Re: List first letter upper case

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.  Register To Reply

6. ## Re: List first letter upper case

"Bob Frolek" <BobFrolek@hotmail.com> 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?  Register To Reply

7. ## Re: List first letter upper case

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:
> "Bob Frolek" <BobFrolek@hotmail.com> 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?  Register To Reply

8. 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.  Register To Reply

9. ## Re: List first letter upper case

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?

"Bob Frolek" <BobFrolek@hotmail.com> 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.  Register To Reply

10. ## Re: List first letter upper case

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?
>
> "Bob Frolek" <BobFrolek@hotmail.com> 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.  Register To Reply