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
Re: Capitalize first letter on sentence
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
Re: Capitalize first letter on sentence
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.
Re: Capitalize first letter on sentence
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
Re: Capitalize first letter on sentence
Ah, I misunderstood your question.
That gets a bit more complicated. Have a look here at what anhn posted.
Re: Capitalize first letter on sentence
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
Re: Capitalize first letter on sentence
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.
Re: Capitalize first letter on sentence
Hi Jordan
Would you like to try this simpler version :)
http://www.vbforums.com/showpost.php...8&postcount=17
Re: Capitalize first letter on sentence
Hi koolsid,
Can you describe step-by-step how running this code?
Thanks
Re: Capitalize first letter on sentence
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
Re: Capitalize first letter on sentence
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.
Re: Capitalize first letter on sentence
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
Re: Capitalize first letter on sentence
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)