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?
string with the first letter capitalized. How can I do this?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
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
=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
then you can paste special values.
Thanks
Gaurav
P
Pete_UK
PROPER will capitalize the first letter of every word.
Pete
Pete
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
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.
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
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))
--
=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
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
Yes, you're right, Paul - thanks for pointing this out.
Pete
Pete
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
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.......
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
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.......
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.