How to get the first word of a letter in upper case using a formula

[KapardhiSarma]

Hi,


I have a requirement to convert the first word of the letter to upper case, and every first word after a '.' should be an upper case.


See my requirement below.


kapardhi.p.v.k should be changed to KapardhiPVK


Please help.

 

[shrivallabha]

Hello KapardhiSarma,


Welcome To Chandorg.


You should've posted more data to get accurate results.


Assuming you are having text in cell A1, try the formula below:

=SUBSTITUTE(PROPER(A1),".","")

 

[KapardhiSarma]

Hi Vallabha,


Thanks for the reply. This helped.


Here's a new requirement.


Say i have my data KapardhiPVK in cell B1, i want all the uppercase letters in C1, like KPVK.


Please help. Thanks.

 

[shrivallabha]

Hello KapardhiSarma,


I'm afraid there'd be no straight formula achieving that but VBA would be easier and simpler to apply. Is this OK with you?

 

[KapardhiSarma]

Thanks a lot for your suggestions and quick responses Vallabha. I am not much in to VBA but please give me the code..ill try to implement it please.


I have small issue here. The formula you have suggested is working fine, but please consider the below.


I have a name JackCBack in cell A1. The fomula is converting this to Jackcback.


I want the formula to retain old data if it does not contain "." in it and change the one with dot as suggested above by you.


Thanks again for the help.

 

[shrivallabha]

OK. Go to Visual Basic Editor and insert a module. See here for how to insert a module:

http://www.jlathamsite.com/Teach/Excel_GP_Code.htm


Paste the following code in the inserted module.

[pre]

Option Explicit
Public Function GetUpperCaseLetters(strInput As String) As String
Dim i As Integer
If strInput <> vbNullString Then
For i = 1 To Len(strInput)
If Mid(strInput, i, 1) = UCase(Mid(strInput, i, 1)) Then
GetUpperCaseLetters = GetUpperCaseLetters & Mid(strInput, i, 1)
End If
Next i
End If
End Function

[/pre]
And then in C1 type like normal formula as below:

=GetUpperCaseLetters(B1)