• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

help putting first letter of each word in the same cell upper case

E

eddyrcabrera79

Member
Hi,

I'm working with a Userform in excel and I found a macro to make the first letter of a word upper case in each cell, with this Userform I'm entering data in each cell.


Code: 
For Each cell In Application.ActiveSheet.UsedRange
    If (cell.Value <> "") Then
    cell.Value = UCase(Left(cell.Value, 1)) & Right(cell.Value, Len(cell.Value) - 1)
    End If
Next

This is what I require:

1 - How do I modify this code in order to make upper case the first letter of each word in each cell in column C and D

2 - In column B I will write a comment in a paragraph form. How can I make the first letter of the first word upper case and also after a dot space Ex:(Hi. How are you?)

Thank you in advance
 
Last edited by a moderator:
Instead of using left/right, you can use the PROPER function.

Formula solution:
=PROPER(A2)

Code solution:
Code: 
Sub ProperWords()
Dim c As Range

Application.ScreenUpdating = False
For Each c In ActiveSheet.Range("C:D").SpecialCells(xlCellTypeConstants)
    c.Value = WorksheetFunction.Proper(c.Value)
Next c
Application.ScreenUpdating = True
End Sub
 
To do like in your part 2:
Code: 
Sub ProperSentences()
Dim c As Range

Application.ScreenUpdating = False
For Each c In ActiveSheet.Range("B:B").SpecialCells(xlCellTypeConstants)
    c.Value = SentenceCase(c.Value)
Next c
Application.ScreenUpdating = True

End Sub

'Function obtained from:
'http://www.mrexcel.com/forum/excel-questions/356800-capitalize-only-first-letter-text-string.html#post1764045

Function SentenceCase(txt As String) As String
Dim e
For Each e In Split(txt, ".")
    SentenceCase = SentenceCase & ". " & UCase(Left$(Trim(e), 1)) & _
        LCase(Mid$(Trim(e), 2))
Next
SentenceCase = Mid$(SentenceCase, 3)
End Function
 
Thank you Luke M for the reply.

I attached a sample doc. I'm not a macro savvy. would you mind inserting the codes in my doc. I inserted the one to make Uppercase the first letter of each word in columns C and D.

1 - Where do I put the code to make column B Uppercase in the first letter of the first word and also after a dot.
2 - because the document will have several columns? where does the code to make first letter of the first word uppercase goes for the rest of the columns

Thank you
 

Attachments

Hi Eddy,

Do you want to run the code when a user adds a new record, or everytime the workbook opens (which seems like overkill), or just a one shot?

If just a one shot, copy all of this to a module, change the range definitions where I comment, and then run the RunMe macro.
Code: 
Sub RunMe()
'this is the macro to run
Dim rngFirst As Range
Dim rngSecond As Range

'Which cells do you want to capitalize every word?
Set rngFirst = Range("C:D")
'Which cells do you onle want to capitalize start of each sentence?
Set rngSecond = Range("B:B")


Application.ScreenUpdating = False
Call ProperWords(rngFirst)
Call ProperSentences(rngSecond)
Application.ScreenUpdating = True
End Sub


Private Sub ProperWords(rngWords As Range)
Dim c As Range

For Each c In rngWords.SpecialCells(xlCellTypeConstants)
    c.Value = WorksheetFunction.Proper(c.Value)
Next c

End Sub
Private Sub ProperSentences(rngSentences As Range)
Dim c As Range

For Each c In rngSentences.SpecialCells(xlCellTypeConstants)
    c.Value = SentenceCase(c.Value)
Next c

End Sub

Function SentenceCase(txt As String) As String
Dim e
For Each e In Split(txt, ".")
    SentenceCase = SentenceCase & ". " & UCase(Left$(Trim(e), 1)) & _
        LCase(Mid$(Trim(e), 2))
Next
SentenceCase = Mid$(SentenceCase, 3)
End Function
 
Just FYI, the Proper worksheetfunction/application method is better at dealing with irregularities (e.g. names like O'Leary) than StrConv is.
 
You must log in or register to reply here.
Back
Top