List first letter upper case

[Bob Frolek]

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?

 

[Bob Umlas]

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

 

[Domenic]

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$11)<=$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$11))),"")

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

Hope this helps!

 

[Bob Frolek]

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

 

[Bob Frolek]

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.

 

[Domenic]

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 Frolek]

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.

 

[mudraker]

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.

 

[Domenic]

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?