Capitalize only first letter and lowercase the rest
This tutorial shows how to lowercase all letters and uppercase only the first letter in a string through the use of an Excel formula, with the UPPER, LEFT, RIGHT, LOWER and LEN functions, or VBA
EXCEL FORMULA 1. Capitalize only first letter and lowercase the rest using the UPPER, LEFT, RIGHT, LOWER and LEN functions
EXCEL
Hard coded formula
Cell reference formula
|
GENERIC FORMULA
=UPPER(LEFT(string,1))&LOWER(RIGHT(string,LEN(string)-1))
ARGUMENTS GENERIC FORMULA
=UPPER(LEFT(string,1))&LOWER(RIGHT(string,LEN(string)-1))
ARGUMENTS EXPLANATION This formula uses the UPPER, LEFT, RIGHT, LOWER and LEN functions to uppercase only the first letter of the first word and lowercase the rest 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 only the first letter of the first word and lowercase the rest entered directly in the formula or referenced to a cell. |
Hard coded against single cell
Sub Capitalize_only_first_letter_and_lowercase_the_rest()
'declare variables
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'capitalise only the first letter of the first word and lowercase the rest in a string
ws.Range("C5").Formula = "=UPPER(LEFT(""bread butter milk"",1))&LOWER(RIGHT(""bread butter milk"",LEN(""bread butter milk"")-1))"
ws.Range("C5").Formula = "=UPPER(LEFT(""bread butter milk"",1))&LOWER(RIGHT(""bread butter milk"",LEN(""bread butter milk"")-1))"
End Sub
Cell reference against single cell
Sub Capitalize_only_first_letter_and_lowercase_the_rest()
'declare variables
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'capitalise only the first letter of the first word and lowercase the rest in a string
ws.Range("C5").Formula = "=UPPER(LEFT(B5,1))&LOWER(RIGHT(B5,LEN(B5)-1))"
ws.Range("C5").Formula = "=UPPER(LEFT(B5,1))&LOWER(RIGHT(B5,LEN(B5)-1))"
End Sub
Hard coded against range of cells
Sub Capitalize_only_first_letter_and_lowercase_the_rest()
'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 and lowercase the rest 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))&LOWER(RIGHT(""" & strStringProper & """,LEN(""" & strStringProper & """)-1))"
x = 5
x = x + i
ws.Range("C" & x).Formula = "=UPPER(LEFT(""" & strStringProper & """,1))&LOWER(RIGHT(""" & strStringProper & """,LEN(""" & strStringProper & """)-1))"
Next i
End Sub
Cell reference against range of cells
Sub Capitalize_only_first_letter_and_lowercase_the_rest()
'declare variables
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'capitalise only the first letter of the first word and lowercase the rest in a string
For i = 5 To 8
ws.Range("C" & i).Formula = "=UPPER(LEFT(B" & i & ",1))&LOWER(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 | |
| Capitalize only first letter | How to uppercase only the first letter 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 | |
| LOWER Function | The Excel LOWER function converts all uppercase text in a specified text string to lowercase | |
| 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 |