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