Capitalize first letter in sentence

  • Thread starter Thread starter Lightjag
  • Start date Start date
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?
 
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
 
Assuming your list is in column A. in B1, type =PROPER(A1) and copy down.
then you can paste special values.

Thanks
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
 
PROPER will capitalize the first letter of each word, while Pete's formula
capitalizes just the first word of the string.
 
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
 
I just realized the other formula is missing one too.
=UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1))



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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Split astring using Capital letter as identifier 12
how to keep a word of same sentence starting w a capital next lin 1
separate a Capital starting word from a sentence 3
Capitalizing first word only... 4
Text to Coloumns, by consecutive capital letters 2
allowing each value to be used once 9
normal into Capital letters automatically 6
Capitalize first letter when type a name in each cell. 6

Back
Top