Capitalize first letter in sentence

L

Lightjag

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?
 
P

Pete_UK

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
 
G

Gaurav

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

Thanks
Gaurav
 
G

Gaurav

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
 
D

David Biddulph

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

PCLIVE

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
 
P

PCLIVE

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



--
 
G

Gord Dibben

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
 
P

Pete_UK

Gord,

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

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

Pete
 
D

Dealmakerjc

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.......
 
G

Gord Dibben

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
 
G

Gaurav

Glad it helped you.


Dealmakerjc said:
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.......
Click to expand...
 
Top