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