Change Text Case – Upper Lower in Excel VBA

Home/Excel VBA/Change Text Case – Upper Lower in Excel VBA

Description:

We can automate task to Change Text Case – Upper Lower in Excel VBA. See the following examples to know how to do this.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


Change Text Case – Upper Lower in Excel VBA: Solution

Change Text Case - Upper Lower in Excel VBA We can use UCase and LCase function to change a text into Upper and Lower.

Examples

Following are the examples to show you how to do this practically.

Sub sbChangeCASE()
'Upper Case
Range("A3") = UCase(Range("A3"))
'Lower Case
Range("A4") = LCase(Range("A4"))
End Sub
Instructions:

Follow the below instructions to do it yourself.

  1. Open an excel workbook
  2. Add some text at A3 and A4 ranges
  3. Press Alt+F11 to open VBA Editor
  4. Insert a Module for Insert Menu
  5. Copy the above code and Paste in the code window
  6. Save the file as macro enabled workbook
  7. Press F5 to execute it
Practical Applications:

Most of the times we use UCASE and LCASE function while comparing the text. For examples if we have two Columns and want to compare the Cells of each columns to check if they are same.

Let’s assume the following data is there in Column A and B, and we want to compare it and print it in Column C.

A B
Apples apples
Banana Bananaa
Red RED
GREEn Green
pink pink

If you write a procedure to compare this data without using UCASE or LCASE function, we may not get the desired results.

See the following Example code and output. We can observe that the its comparing but its case sensitive.

Sub sbCompareColumns_1()
iCntr = 1
Do While Cells(iCntr, 1) <> ""
If Cells(iCntr, 1) = Cells(iCntr, 2) Then
Cells(iCntr, 3) = "Matched"
Else
Cells(iCntr, 3) = "Not Matched"
End If
iCntr = iCntr + 1
Loop
End Sub

You can see the output in Column C:

s

I am using the same code with UCASE function, so that first it will convert to uppercase then compare it. We can avoid the case sensitivity.

Sub sbCompareColumns_2()
iCntr = 1
Do While Cells(iCntr, 1) <> ""
If UCase(Cells(iCntr, 1)) = UCase(Cells(iCntr, 2)) Then
Cells(iCntr, 3) = "Matched"
Else
Cells(iCntr, 3) = "Not Matched"
End If
iCntr = iCntr + 1
Loop
End Sub

You can see the output in Column C is different than the above result.

LIMITED TIME OFFER
By |February 9th, 2013|Excel VBA|8 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

8 Comments

  1. gcpath March 2, 2014 at 2:26 AM - Reply

    i tried the same but i am getting error as “variable not defined”

    • gcpath March 2, 2014 at 2:30 AM - Reply

      tried Dim iCntr it worked

      thanksss…

  2. Immanuel March 19, 2014 at 9:39 PM - Reply

    When I copy the below code in to the VB editor .. the syntax lines come up as red, and pops up an error
    Sub sbChangeCASE()
    ‘Upper Case
    Range("A3") = UCase(Range("A3"))

    ‘Lower Case
    Range("A4") = LCase(Range("A4"))
    End Sub

    • PNRao March 20, 2014 at 1:27 AM - Reply

      Please try this, the single quote is for commenting the lines, you can remove it.

      Sub sbChangeCASE()
      'Upper Case
      Range("A3") = UCase(Range("A3"))

      'Lower Case
      Range("A4") = LCase(Range("A4"))
      End Sub

      Thanks-PNRao!

  3. immanuel March 21, 2014 at 8:05 PM - Reply

    Hi,
    Is iCntr the same this as inStr ….returning value of the occurring string..
    I don’t quite get the below script

    Sub sbCompareColumns_1()
    iCntr = 1
    Do While Cells(iCntr, 1) “”
    If Cells(iCntr, 1) = Cells(iCntr, 2)
    Then Cells(iCntr, 3) = “Matched”
    Else Cells(iCntr, 3) = “Not Matched”
    End If
    iCntr = iCntr + 1
    LoopEnd
    Sub

    • PNRao March 22, 2014 at 12:49 AM - Reply

      Hi,
      Please see the explained code below:

      Sub sbCompareColumns_1()
      iCntr = 1
      'this will loop until the the Column A is blank
      Do While Cells(iCntr, 1) <> ""
      'Here it is comparing the values
      If Cells(iCntr, 1) = Cells(iCntr, 2) Then
      Cells(iCntr, 3) = "Matched"
      Else
      Cells(iCntr, 3) = "Not Matched"
      End If
      'moving to next cell
      iCntr = iCntr + 1
      Loop
      End Sub

      Thanks-PNRao!

  4. Sitharth May 6, 2015 at 5:09 PM - Reply

    Thanks -PNRao……

  5. Bhukailas October 24, 2016 at 4:25 PM - Reply

    Is there any short cut to toggle case of entered text in Cells.

Leave A Comment


Related pages


automatically hide rows in excelexcel dashboards and reports 2010autofilter excel 2007vba array uboundhow to add extra rows in excellistbox additem vbaiferror in vlookupget rid of duplicates in excelpivot table graphhow to merge cells in excel with data in themexcel remove blankhow to add multiple rows in excelms sql developer interview questions and answersexcel vba user formproject management spreadsheet templatems project shortcut keysrows vbaworksheet select vbadelete excel worksheetmessage vbahow to do multiple if statements in excelexcel macro active cellworkbooks open filename errorsql technical interview questions and answersexcel check duplicateslookup value in excelsas predictive modeling interview questionsconditional statements in exceluser input in vbalearn excel macros step by stepexcel vba lengthexcel vba open word document3d excel graphexcel import data from another workbookfinding duplicates in excel 2010alternative to vlookupvba instrexcel find duplicate recordsvisual basic excel macrofind duplicates in excel listadodb.connectionhow to delete duplicates in excel 2010creating an if statement in excelexcel vba inputbox cancelunprotect worksheet forgot passwordprotect password excelhow to insert a dropdown in excelmacro to unlock excel workbookexcel vba ebook pdfms excel short keysrunning a macro in excelexcel macro password removervba lower caseunmergevba indentchange the width of a column in excelvba delete blank rowsvba scripting.filesystemobjectvba scripting in excelvba change font colorvlookup function excel 2010vba tutorial excel 2007excel delete named rangeexcel vba formataccess vba dirworksheet functionscool excel sheetsvba input boxexcel formula hlookup exampleexcel spreadsheet cost analysiscase statement vba accessexcel vba protect workbookhow to unprotect a workbookexcel vba report examplelearn excel vba step by stepvba excel color cellvb basic codesexcel 3d plots