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 FORMULA 1. Capitalize all letters except for the first letter using the UPPER, LEFT, RIGHT and LEN functions

EXCEL

Hard coded formula
Capitalize all letters except for the first letter
Cell reference formula
Capitalize all letters except for the first letter
=LEFT("bread butter milk",1)&UPPER(RIGHT("bread butter milk",LEN("bread butter milk")-1))
=LEFT(B5,1)&UPPER(RIGHT(B5,LEN(B5)-1))
GENERIC FORMULA

=LEFT(string,1)&UPPER(RIGHT(string,LEN(string)-1))

ARGUMENTS
string: A string in which you want to capitalize all letters except for the first letter.

GENERIC FORMULA

=LEFT(string,1)&UPPER(RIGHT(string,LEN(string)-1))

ARGUMENTS
string: A string in which you want to capitalize all letters except for the first letter.

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.

VBA CODE 1. Capitalize all letters except for the first letter using VBA

VBA

Hard coded against single cell
Sub Capitalize_all_letters_except_for_the_first_letter()
'declare variables
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))"

End Sub

Cell reference against single cell
Sub Capitalize_all_letters_except_for_the_first_letter()
'declare variables
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))"

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
Set ws = Worksheets("Analysis")
strString(0) = "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))"
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
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
How to capitalize the first letter of each word in a string through the use of an Excel formula or VBA
How to uppercase only the first letter in a string through the use of an Excel formula or VBA
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
The Excel UPPER function converts all lowercase text in a specified text string to uppercase
The Excel LEFT function returns the specified number of characters from a specified string, starting from the left side
The Excel RIGHT function returns the specified number of characters from a specified string, starting from the right side
The Excel LEN function returns the number of characters in a specified string