change first letter only to Upper

[JB]

Hello
Can someone please tell me the formula to change just the first letter of a
sentence in a cell to upper case.
I know =proper or upper and I know =Left but can't seem the get them to
marry up
Thanks

 

[Don Guillett]

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

 

[JB]

Halleluiah!!
Thank you so much

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Rick Rothstein]

I don't know if this is "faster" or not, but it is one function call less
and involves no concatenation...

=REPLACE(F5,1,1,UPPER(LEFT(F5)))

 

[Shane Devenshire]

Hi,

We have an interpretation problem with what your question means, for
example, how do you want to handle this:

This Is A Brown FOX

Maybe we should use

=REPLACE(LOWER(A1),1,1,UPPER(LEFT(A1)))
or
=UPPER(LEFT(A1))&LOWER(MID(A1,2,9^4))

this suggests that we could shorten the first suggestion too, if you don't
care about the case of the rest of the sentence:

=UPPER(LEFT(A1))&MID(A1,2,9^2)

And if you are concerned with speed and function calls replace 9^2 with 1E4:

=UPPER(LEFT(A1))&LOWER(MID(A1,2,1E4))

=UPPER(LEFT(A1))&MID(A1,2,1E4)

 

[Khalil Handal]

This will be usefull in the case of names such as: Don Guillet
What code should be used in the case if I typed don guillet in cell C2 and
it will be changed to Don Guillet???

 

[Don Guillett]

Place this in a regular module and then =uew(a1)

Function uew(x)
uew = StrConv(x, vbProperCase)
'vbProperCase = Converts the first letter of every word
'in string to uppercase.
End Function

 

[JB]

Love it!
The sentences in each cell in these case have about 20 -30 letters. They
are descriptions so only the first Letter needs to be Upper case.
But I do come across all these different scenarios so all will come in
handy.
Thank you for your time.
Jenny

 

[Rick Rothstein]

Why not use the built-in PROPER function instead?

=PROPER(A1)

 

[Don Guillett]

don guil 1lett

 

[Rick Rothstein]

Okay, but there are possible problems with your function as well. Consider
this (perhaps more likely) usage...

maj. gen. (ret.) don c. morrow

See the (Ret.) usage here...

http://www.encyclopedia.com/doc/1G1-155405018.html

 

[Don Guillett]

http://www.google.com/search?sourceid=navclient&ie=UTF-8&rlz=1T4GFRD_en___US219&q=gen+ret

 

[Rick Rothstein]

So I'm guessing, then, that the sites shown at your posted link used your
function to write their text.<vbg> Actually, when I did my search, I used
"(ret.)" and came up with an entirely different set of links.

Anyway, that is interesting as I seem to remember always seeing the (Ret.)
with an upper case 'R'. It's been awhile since my military days (say, some
40+ years), but that is how I remembered it. Perhaps usage has changed over
the years?

 

[Rick Rothstein]

Actually, another example could be something like this...

john jones (mary's father).

I guess the point I'm trying to make is that there is not one, universal
function that can handle all the possible ways text can be presented and it
is incumbent on the user to know his/her data.

 

[Shane Devenshire]

you can't spell any better than me, D0N

 

[Don Guillett]

I know. It's terrible. I am trying to learn how.