Excel - Capitalize first letter in sentence

Asked By Lightja

14-Jan-08 05:13 PM

I am referencing a text string in excel, and would like to return the text
string with the first letter capitalized.  How can I do this?

14-Jan-08 10:17 PM

Like this:

=3DUPPER(LEFT(A1,1)&RIGHT(A1,LEN(A1)-1)

This leaves the rest of the string unchanged and capitalises the first
letter. You may like to add this amendment:

=3DUPPER(LEFT(A1,1)&LOWER(RIGHT(A1,LEN(A1)-1))

which will ensure that the rest of the string is lower case.

Hope this helps.

Pete

On Jan 14, 10:13=A0pm, Lightjag <Light...@>

14-Jan-08 05:29 PM

Assuming your list is in column A. in B1, type =PROPER(A1) and copy down.
then you can paste special values.

Thanks
Gaurav

14-Jan-08 05:36 PM

Hey Pete,

Just curious. Does the formula you mentioned do anything extra than the
PROPER function? Just increasing my knowledge.

Thanks
Gaurav


Like this:

=UPPER(LEFT(A1,1)&RIGHT(A1,LEN(A1)-1)

This leaves the rest of the string unchanged and capitalises the first
letter. You may like to add this amendment:

=UPPER(LEFT(A1,1)&LOWER(RIGHT(A1,LEN(A1)-1))

which will ensure that the rest of the string is lower case.

Hope this helps.

Pete

On Jan 14, 10:13 pm, Lightjag <Light...@>

14-Jan-08 10:17 PM

PROPER will capitalize the first letter of every word.

Pete

.
t
oted text -

14-Jan-08 05:39 PM

PROPER will capitalize the first letter of each word, while Pete's formula
capitalizes just the first word of the string.
--
David Biddulph

14-Jan-08 05:54 PM

Peter,

I think your first suggestion is missing a ")".  I'm thinking it should be:
=UPPER(LEFT(A1,1))&RIGHT(A1,LEN(A1)-1)

Regards,
Paul

--

Like this:

=UPPER(LEFT(A1,1)&RIGHT(A1,LEN(A1)-1)

This leaves the rest of the string unchanged and capitalises the first
letter. You may like to add this amendment:

=UPPER(LEFT(A1,1)&LOWER(RIGHT(A1,LEN(A1)-1))

which will ensure that the rest of the string is lower case.

Hope this helps.

Pete

On Jan 14, 10:13 pm, Lightjag <Light...@>

14-Jan-08 05:56 PM

I just realized the other formula is missing one too.
=UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1))



--

14-Jan-08 07:22 PM

What am I doing wrong?

Both of these leave me with all upper case.

=UPPER(LEFT(A1,1)&RIGHT(A1,LEN(A1)-1))

=UPPER(LEFT(A1,1)&LOWER(RIGHT(A1,LEN(A1)-1)))

A1 contains      this is some text


Gord Dibben  MS Excel MVP

On Mon, 14 Jan 2008 22:39:40 -0000, "David Biddulph" <groups [at]

14-Jan-08 10:17 PM

Yes, you are right, Paul - thanks for pointing this out.

Pete

d

ed text -

14-Jan-08 10:17 PM

Gord,

Paul Clive has pointed out that I missed a bracket off - should be:

=3DUPPER(LEFT(A1,1))&RIGHT(A1,LEN(A1)-1)
and
=3DUPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1))

Pete

a

14-Jan-08 10:15 PM

Gaurav, mate your blood's worth bottling. I have wasted hours to sort this
out until I saw your post on how to do it. As I have a list of over 600 names
and addresses in capitals I need to convert back to Title case, this will
make it a breeze.......

15-Jan-08 12:08 AM

Yep.

Finally got the right combination of brackets.

I had the doubles in the wrong place and you had not enough<g>

Thanks to PCLIVE and Pete


Gord

15-Jan-08 09:25 AM

Glad it helped you.