Capitalize only first letter
This tutorial shows how to uppercase only the first letter through the use of an Excel formula, with the UPPER, LEFT, RIGHT and LEN functions, or VBA
Hard coded formula
Cell reference formula
|
GENERIC FORMULA
=UPPER(LEFT(string,1))&RIGHT(string,LEN(string)-1)
ARGUMENTS GENERIC FORMULA
=UPPER(LEFT(string,1))&RIGHT(string,LEN(string)-1)
ARGUMENTS EXPLANATION This formula uses the UPPER, LEFT, RIGHT and LEN functions to uppercase the first letter of the first word in a string.
Click on either the Hard Coded or Cell Reference button to view the formula that either has the string in which you want to capitalize the first letter of the first word entered directly in the formula or referenced to a cell. |
Hard coded against single cell
Sub Capitalize_only_first_letter()
'declare variables
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'capitalise only the first letter of the first word in a string
ws.Range("C5").Formula = "=UPPER(LEFT(""bread butter milk"",1))&RIGHT(""bread butter milk"",LEN(""bread butter milk"")-1)"
ws.Range("C5").Formula = "=UPPER(LEFT(""bread butter milk"",1))&RIGHT(""bread butter milk"",LEN(""bread butter milk"")-1)"
End Sub
Cell reference against single cell
Sub Capitalize_only_first_letter()
'declare variables
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'capitalise only the first letter of the first word in a string
ws.Range("C5").Formula = "=UPPER(LEFT(B5,1))&RIGHT(B5,LEN(B5)-1)"
ws.Range("C5").Formula = "=UPPER(LEFT(B5,1))&RIGHT(B5,LEN(B5)-1)"
End Sub
Hard coded against range of cells
Sub Capitalize_only_first_letter()
'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 only the first letter of the first word in a string
For i = 0 To 3
strStringProper = strString(i)
x = 5
x = x + i
ws.Range("C" & x).Formula = "=UPPER(LEFT(""" & strStringProper & """,1))&RIGHT(""" & strStringProper & """,LEN(""" & strStringProper & """)-1)"
x = 5
x = x + i
ws.Range("C" & x).Formula = "=UPPER(LEFT(""" & strStringProper & """,1))&RIGHT(""" & strStringProper & """,LEN(""" & strStringProper & """)-1)"
Next i
End Sub
Cell reference against range of cells
Sub Capitalize_only_first_letter()
'declare variables
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'capitalise only the first letter of the first word in a string
For i = 5 To 8
ws.Range("C" & i).Formula = "=UPPER(LEFT(B" & i & ",1))&RIGHT(B" & i & ",LEN(B" & i & ")-1)"
Next i
End Sub
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Capitalize first letter of each word | How to capitalize the first letter of each word in a string through the use of an Excel formula or VBA |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| UPPER Function | The Excel UPPER function converts all lowercase text in a specified text string to uppercase | |
| LEFT Function | The Excel LEFT function returns the specified number of characters from a specified string, starting from the left side | |
| RIGHT Function | The Excel RIGHT function returns the specified number of characters from a specified string, starting from the right side | |
| LEN Function | The Excel LEN function returns the number of characters in a specified string |