Capitalize first letter on sentence

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

Welcome to the forums. :wave:

Is this what you mean?

Code:

---

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

---

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.

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

Ah, I misunderstood your question.

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

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

Quote:

---

Originally Posted by Hack

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.

Hi Jordan

Would you like to try this simpler version :)

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

Hi koolsid,

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

Thanks

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...

vb Code:

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

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

Test your function with this:

Code:

---

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

---

and then

Code:

---

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

---

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

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

vb Code:

'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...

vb Code:

'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)