Capitalize first letter of each word
This tutorial shows how to uppercase the first letter of each word from a string through the use of an Excel formula, with the PROPER function, or VBA
Hard coded formula
Cell reference formula
|
=PROPER("bread butter milk")
=PROPER(B5)
|
GENERIC FORMULA
=PROPER(string)
ARGUMENTS GENERIC FORMULA
=PROPER(string)
ARGUMENTS EXPLANATION This formula uses the PROPER function to uppercase the first letter of each word from a string.
Click on either the Hard Coded or Cell Reference button to view the formula that either has the string that contains the words in which you want to capitalize the first letter of each word entered directly in the formula or referenced to a cell. |
Hard coded against single cell
Sub Capitalize_first_letter_of_each_word()
'declare variables
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'capitalise the first letter of each word in a string
ws.Range("C5") = WorksheetFunction.Proper("bread butter milk")
ws.Range("C5") = WorksheetFunction.Proper("bread butter milk")
End Sub
Cell reference against single cell
Sub Capitalize_first_letter_of_each_word()
'declare variables
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'capitalise the first letter of each word in a string
ws.Range("C5") = WorksheetFunction.Proper(ws.Range("B5"))
ws.Range("C5") = WorksheetFunction.Proper(ws.Range("B5"))
End Sub
Hard coded against range of cells
Sub Capitalize_first_letter_of_each_word()
'declare variables
Dim ws As Worksheet
Dim strString(4) As String
Dim ws As Worksheet
Dim strString(4) As String
Set ws = Worksheets("Analysis")
strString(0) = "bread butter milk"
strString(1) = "BRead BUtter MIlk"
strString(2) = "BREAD BUTTER MILK"
strString(3) = "breAD buttER miLK"
strString(1) = "BRead BUtter MIlk"
strString(2) = "BREAD BUTTER MILK"
strString(3) = "breAD buttER miLK"
'capitalise the first letter of each word in a string
For i = 0 To 3
strStringProper = strString(i)
x = 5
x = x + i
ws.Range("C" & x) = WorksheetFunction.Proper(strStringProper)
x = 5
x = x + i
ws.Range("C" & x) = WorksheetFunction.Proper(strStringProper)
Next i
End Sub
Cell reference against range of cells
Sub Capitalize_first_letter_of_each_word()
'declare variables
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'capitalise the first letter of each word in a string
For i = 5 To 8
ws.Range("C" & i) = WorksheetFunction.Proper(ws.Range("B" & i))
Next i
End Sub
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Change lowercase to uppercase | How to change lowercase into uppercase for a specific text string through the use of an Excel formula or VBA |