| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Capitalize first letter in sentence
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capitalize first letter in sentence
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 wrote: 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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capitalize first letter in sentence
Assuming your list is in column A. in B1, type =PROPER(A1) and copy down.
then you can paste special values. Thanks Gaurav "Lightjag" wrote in message ... 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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capitalize first letter in sentence
PROPER will capitalize the first letter of every word.
Pete On Jan 14, 10:29*pm, "Gaurav" wrote: Assuming your list is in column A. in B1, type =PROPER(A1) and copy down.. then you can paste special values. Thanks Gaurav "Lightjag" wrote in message ... 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?- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capitalize first letter in sentence
Hey Pete,
Just curious. Does the formula you mentioned do anything extra than the PROPER function? Just increasing my knowledge. Thanks Gaurav "Pete_UK" wrote in message ... 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 wrote: 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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capitalize first letter in sentence
PROPER will capitalize the first letter of each word, while Pete's formula
capitalizes just the first word of the string. -- David Biddulph "Gaurav" wrote in message ... Hey Pete, Just curious. Does the formula you mentioned do anything extra than the PROPER function? Just increasing my knowledge. Thanks Gaurav "Pete_UK" wrote in message ... 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 wrote: 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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capitalize first letter in sentence
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 -- "Pete_UK" wrote in message ... 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 wrote: 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? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capitalize first letter in sentence
I just realized the other formula is missing one too.
=UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1)) -- "PCLIVE" wrote in message ... 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 -- "Pete_UK" wrote in message ... 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 wrote: 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? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capitalize first letter in sentence
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] biddulph.org.uk wrote: PROPER will capitalize the first letter of each word, while Pete's formula capitalizes just the first word of the string. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capitalize first letter in sentence
Yes, you're right, Paul - thanks for pointing this out.
Pete On Jan 14, 10:56*pm, "PCLIVE" wrote: I just realized the other formula is missing one too. =UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1)) -- "PCLIVE" wrote in message ... 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 -- "Pete_UK" wrote in message ... 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 wrote: 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?- Hide quoted text - - Show quoted text - |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capitalize first letter in sentence
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 On Jan 15, 12:22*am, Gord Dibben <gorddibbATshawDOTca wrote: 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] biddulph.org.uk wrote: PROPER will capitalize the first letter of each word, while Pete's formula capitalizes just the first word of the string.- Hide quoted text - - Show quoted text - |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capitalize first letter in sentence
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....... "Gaurav" wrote: Assuming your list is in column A. in B1, type =PROPER(A1) and copy down. then you can paste special values. Thanks Gaurav "Lightjag" wrote in message ... 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? |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capitalize first letter in sentence
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 On Mon, 14 Jan 2008 16:48:48 -0800 (PST), Pete_UK wrote: 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))&RIGHT(A1,LEN(A1)-1) Pete On Jan 15, 12:22*am, Gord Dibben <gorddibbATshawDOTca wrote: 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] biddulph.org.uk wrote: PROPER will capitalize the first letter of each word, while Pete's formula capitalizes just the first word of the string.- Hide quoted text - - Show quoted text - |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capitalize first letter in sentence
Glad it helped you.
"Dealmakerjc" wrote in message ... 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....... "Gaurav" wrote: Assuming your list is in column A. in B1, type =PROPER(A1) and copy down. then you can paste special values. Thanks Gaurav "Lightjag" wrote in message ... 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I capitalize the first letter of multiple cells? | Excel Discussion (Misc queries) | |||
| Capitalize | Excel Discussion (Misc queries) | |||
| capitalize | Excel Discussion (Misc queries) | |||
| Sentence with first letter in Capital, rest all in small. | Excel Discussion (Misc queries) | |||
| Capitalize first letter when type a name in each cell. | Excel Worksheet Functions | |||
Linear Mode
