Capitalize first letter on sentence

[Jordan2008]

I'm using Excel and I don't have experience creating macros and scripts.

I need to capitalize the first letter of each sentence, for example:

"Capital first letter only. After Period Should be capitalized only The First Letter"

to this:

"Capital first letter only. After period should be capitalized only the first letter"

How I do that? - a step-by-step will be really appreciated. I'm sorry I'm not a programmer, I just need help.

thanks

[Hack]

Welcome to the forums.

Is this what you mean?

Private Sub Command1_Click()
Dim strString As String
strString = "this is the string I will use."
strString = StrConv(strString, vbProperCase)
MsgBox strString
End Sub

[Fizziii]

Hack, I copied and pasted your code (I hadn't seen vbProperCase before and was intrigued) into a new Excel sheet, and stepped through it and it capitalized the first letter of every word.

[Jordan2008]

Hack,
Thank you for your help.

The code capitalize first letter each word in the string. I'm not sure if doing right, I went to Vb and paste the code and hit F5. I not sure how implement it in excel.

Thanks

[Hack]

Ah, I misunderstood your question.

That gets a bit more complicated. Have a look here at what anhn posted.

[Jordan2008]

Thank you.

Do you know if there is a step-by-step to implement this. I guess this is what I need but don't know how to do it.

I'm sorry I'm not programmer.

Thanks

[anhn]

That gets a bit more complicated. Have a look here at what anhn posted.

Indeed, that thread is quite complicated.

Another post of mine in CodeBank to do SentenceCase here.

[Siddharth Rout]

Hi Jordan

Would you like to try this simpler version

http://www.vbforums.com/showpost.php...8&postcount=17

[Jordan2008]

Hi koolsid,

Can you describe step-by-step how running this code?

Thanks

[Siddharth Rout]

Definitely


I have commented the code so that it is easy to understand. how do you want to use it. Maybe I can explain a little bit more after that...

Sub ConvertToSentenceCase()
'Declare variables
Dim TestString As String, strg2 As String
 
'Example string that you want to convert to sentence case
'Replace this string with your string
TestString = "wake me up before you go go. i want to have my breakfast! don't forget."
 
'test the 1st 10 chars to get the first - incase the selection includes
'some spaces at the beginning.
For X = 1 To 10
  strg2 = Mid(TestString, X, 1)
  If strg2 Like "[a-zA-Z]" Then
    Mid(TestString, X, 1) = UCase(strg2)
    Exit For
  End If
Next X
 
'test criteria (.!?) for the end of a sentence, then Capitalize the next letter.
For X = X To Len(TestString)
  strg2 = Mid(TestString, X, 1)
  If strg2 Like "[.!?]" Then
    For Y = X + 1 To X + 10
      strg2 = Mid(TestString, Y, 1)
      If strg2 Like "[a-zA-Z]" Then
        Mid(TestString, Y, 1) = UCase(strg2)
        X = Y + 1
        Exit For
      End If
    Next Y
  End If
Next X
 
'This will give you the result
MsgBox TestString
 
End Sub

[anhn]

What make your function simpler? Is it faster or slower? Why 10?

Test your function with this:

TestString = "1.          wake me up. 2.    before YOu go. 12/09/2008: you Are dreaming.

and then

TestString = "1. wake me Up" & vbCrLf & "2. before you go. 12/09: you ARE dreaming.

[Jordan2008]

Well, I have a database and several columns are text cells. The text is like "All Words Start With..." and I want "All words start with....".
The rows are over 5000.

I know that going to VB editor I can enter your code, then return to excel, but then what?

Suppossed I want to fix the problem in all cells in column A. What are the steps to do that?

Thanks

[Siddharth Rout]

In that case you can change the above sub to a function and then use it to convert the case of the text while looping through your records...

Let me change the code for you

Edit:
Okay here it is

'You can use the function "ConvertToSentenceCase" to change the
'case of the text fields in the records
Sub changecase()
    Dim TestString As String
    TestString = "wake me up before you go go. i want to have my breakfast! don't forget."
    'you can loop here through the records of the database and then
    'convert them to sentence case
    MsgBox ConvertToSentenceCase(TestString)
End Sub

Paste the code below in a module...

'Paste this in a module
Function ConvertToSentenceCase(TestString As String) As String
'Declare variables
Dim strg2 As String
 
'test the 1st 10 chars to get the first - incase the selection includes
'some spaces at the beginning.
For X = 1 To 10
  strg2 = Mid(TestString, X, 1)
  If strg2 Like "[a-zA-Z]" Then
    Mid(TestString, X, 1) = UCase(strg2)
    Exit For
  End If
Next X
 
'test criteria (.!?) for the end of a sentence, then Capitalize the next letter.
For X = X To Len(TestString)
  strg2 = Mid(TestString, X, 1)
  If strg2 Like "[.!?]" Then
    For Y = X + 1 To X + 10
      strg2 = Mid(TestString, Y, 1)
      If strg2 Like "[a-zA-Z]" Then
        Mid(TestString, Y, 1) = UCase(strg2)
        X = Y + 1
        Exit For
      End If
    Next Y
  End If
Next X
ConvertToSentenceCase = TestString
End Function

If you have any queries, do ask

Edit Sorry I missed the question you had on Column A

After you have pasted the function in the module then type this formula in column B, Say cell B1

=ConvertToSentenceCase(A1)