auto convert into upper case

In this article you will learn how to convert any string into Uppercase, Lowercase and Proper case in Excel. 

You will also learn data validation for only Uppercase entry or in any text case and automatic Uppercase in Excel. 

You will also see Excel VBA UDF for converting specific word or substring in a string to particular text case. 

Also, you will see custom Excel function for getting Abbreviation of a string. 

Capitalize letters in Excel using Upper function

In Excel Upper function is used to Capitalize (make all letters in uppercase) all the letters of a given text.

Upper function takes only one input which is text or cell reference containing text value.

Syntax: UPPER(text)

In the below image cell B2 formula is =UPPER(A2)

You can also supply hard value like this =UPPER(“rohit das”) , it will output ROHIT DAS.

Lower function in Excel (make all letters small)

You can use Lower Excel function to change case of the text to lowercase. It makes all the letters of a text small.

Syntax: LOWER(text)

In the below image cell B2 formula is =LOWER(A2).

You can also use hard value like this =LOWER(“HAMANT DUBEY”) will output hamant dubey.

Proper function in Excel (only first letter of a word capital)

You can use Excel Proper function to capitalize the first letter of words in a given string.

Syntax: PROPER(text)

In the below image cell B2 formula is =PROPER(A2).

You can also use hard value like this =PROPER(“hamant dubey”) will output Hamant Dubey.

Restrict entry in specific case only using Data Validation

What if you want to force the user to enter data in specific case only in Excel cell.

Here I will show you how to do this using Excel Data Validation.

The below image shows data validation for Upper case entry only.

1. Select the range on which you want to apply Data Validation. In the below image it is from A2 to A6. Make sure the first cell is active in selection.
2. Click on Data tab. In the Data tab click Data Validation option.
3. In Data Validation pane from Allow drop down list select Custom. And in formula section enter =EXACT(UPPER(A2),A2) formula. Click on Ok.

Exact Excel function compares two text strings case wise and outputs True or False. Exact(“ab”,”AB”) will output False and Exact(“AB”,”AB”)  will output True.

Suppose user is entering small “t”. This is how data validation rule or formula will be evaluated Exact(Upper(“t”),”t”) becomes Exact(“T”,”t”) which will output False.

Since data entry is allowed when condition becomes True, hence entry will be not allowed for small “t”.

If you want to force Lower case entry only then use the below formula

=EXACT(LOWER(A2),A2)

Data Validation formula for Proper case entry only

 =EXACT(PROPER(A2),A2) 

Optionally, you can write Error Alert message by clicking on Error Alter tab of data validation pane. 

This text message will appear when entry is wrong or not as per rule.

Automatic uppercase entry in Excel

Forcing entry in specific case using Data Validation is good but not very clean solution and user may find it annoying after entering data in other text case.

Suppose you want to make entries in Uppercase only in a specific range. 

Here I will show you solution where entered value will auto get converted to Uppercase (or in any specified text case) even if your entry is in lowercase or any other text case.

Below is the Excel VBA code for automatic uppercase in Excel

Private Sub Worksheet_Change(ByVal Target As Range)
 On Error GoTo JMP
 Application.EnableEvents = False
       Dim entryRng As Range
       Set entryRng = Range("A2:A6")
          If Not Intersect(Target, entryRng) Is Nothing Then
                Target.Value = UCase(Target.Value)
          End If
JMP:
 Application.EnableEvents = True
End Sub

To use the above code press ALT+F11 to open VBA IDE. In VBA IDE double click on sheet  in which you want auto text case change. 

(in my case it is “Autoupper” sheet as per the below image)

Copy the above code section and paste it in VBA IDE of that sheet. Save your workbook either in binary (.xlsb) or in macro  (.xlsm) format.

You can change Range(“A2:A6”) to some another range.

 Above code section is for auto conversion in to Uppercase (Ucase(Target.Value) .

To auto convert into lowercase replace UCase with LCase.

To auto convert into proper case replace UCase(Target.Value) with StrConv(Target.Value, vbProperCase).

VBA UDF to change case of specific word or letter in a string

Sometimes you may want to change the case of a specific word or substring in a string. 

For example in the given string “I love india. Tajmahal is in india.”, you want “india” to get converted into proper case “India” or in uppercase “INDIA” then this solution is perfect for your requirement. 

To create below SubStrCaseChange  VBA UDF press ALT+F11 to open VBA IDE. In VBA IDE go to Insert tab and click on Module to create new standard module. Now copy paste below code section in that module.

Make sure to save as your workbook either in .xlsb (binary) or .xlsm (macro) format.

Don’t worry about code working or code length, just do as instructed and everything will workout easily.

Function SubStrCaseChange(txt As String, subStr As Variant, _
txtCase As String, Optional caseSensitive As Boolean = False, _
Optional exactToSubStr As Boolean = False)
 Dim sStrAr() As Variant, sStr As Variant, fstLp As Boolean
 Dim vbComp As VbCompareMethod, newStr As String
 Dim nSubStr As String
 fstLp = True
 If IsObject(subStr) Then
   sStrAr = rngToArr(subStr)
 Else
   ReDim Preserve sStrAr(0)
   sStrAr(0) = subStr
 End If
 If caseSensitive Then
  vbComp = vbBinaryCompare
 Else
   vbComp = vbTextCompare
 End If
   For Each sStr In sStrAr
     If exactToSubStr Then
      nSubStr = sStr
     Else
      nSubStr = caseChng(sStr, txtCase)
     End If
     If fstLp Then
       newStr = Replace(txt, sStr, nSubStr, compare:=vbComp)
     Else
       newStr = Replace(newStr, sStr, nSubStr, compare:=vbComp)
     End If
     fstLp = False
   Next sStr
  SubStrCaseChange = newStr
End Function
Private Function caseChng(sStr As Variant, txtCase As String) As String
   If LCase(txtCase) = "u" Then
       caseChng = UCase(sStr)
   ElseIf LCase(txtCase) = "l" Then
       caseChng = LCase(sStr)
   Else
       caseChng = StrConv(sStr, vbProperCase)
   End If
End Function
Private Function rngToArr(rng As Variant) As Variant()
   If rng.Columns.Count = 1 Then
      rngToArr = Application.Transpose(rng.Value)
   Else
      rngToArr = Application.Transpose(Application.Transpose(rng.Value))
   End If
End Function

SubStrCaseChange VBA UDF has five parameters.

SubStrCaseChange  Syntax:

SubStrCaseChange(string, subString,intoCase, [caseSensitive match] , [exactToSubStr])

1st three inputs are compulsory and last two are optional.

intoCase can take any one of the value “u” or “l” or “p”. Here “u” means uppercase, “l” lowercase and “p” means proper case.

[caseSensitive match] and [exactToSubStr] have True or False values. For both default value is false.

Instead of explaining all the inputs word by word, I will try to explain it visually using images. 

Abbreviation Function In Excel | VBA UDF for short form | Text Acronym in Excel

Sometimes you may need to get abbreviation or acronym of a given text. 

This VBA UDF (User Defined Function) can quickly output abbreviation of a text in Excel.

To create below Abbreviation VBA UDF press ALT+F11 to open VBA IDE. In VBA IDE go to Insert tab and click on Module to create new standard module. Now copy paste below code section in that module.

Function Abbreviation(str As Variant) As String
  Dim i As Integer, newStr As String
  Dim wrdArr As Variant  ''Word Array after splitting
  newStr = ""
  wrdArr = Split(Trim(str), " ")
  For i = LBound(wrdArr) To UBound(wrdArr)
    newStr = newStr & Mid(wrdArr(i), 1, 1)
  Next i
  Abbreviation = UCase(newStr)
End Function

Above VBA UDF Abbreviation  just requires only one input text string.

=Abbreviation(“central processing unit”) will output CPU.