Case

%3CLINGO-SUB%20id%3D%22lingo-sub-2167689%22%20slang%3D%22en-US%22%3ECase%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2167689%22%20slang%3D%22en-US%22%3E%3CP%3EChanging%20a%20letter%20to%20its%20opposite%20case.%20Input%20is%20'A'%2C%20output%20should%20be%20'a'.%20And%20vice%20versa.%20Thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2167689%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2167845%22%20slang%3D%22en-US%22%3ERe%3A%20Case%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2167845%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F561330%22%20target%3D%22_blank%22%3E%40MajorHavoc%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3ESince%20you%20want%20to%20automate%20the%20exercise%20so%20that%20your%20need%20is%20VBA%20macro.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUse%20this%20VBA%20code%20as%20Standard%20module.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%0ADim%20Choice%20As%20String%0AChoice%20%3D%20Range(%22F1%22).Value%0A%0AIf%20Target.Cells.Count%20%26gt%3B%201%20Or%20Target.HasFormula%20Then%20Exit%20Sub%0A%0A%20%20%20%20On%20Error%20Resume%20Next%0A%0A%20%20%20%20If%20Not%20Intersect(Target%2C%20Range(%22A1%3AA10%22))%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20Application.EnableEvents%20%3D%20False%0A%20%20%20%20%0A%20%20%20%20If%20Choice%20%3D%20%22Upper%22%20Then%0A%20%20%20%20%20Target%20%3D%20StrConv(Target%2C%20vbUpperCase)%0A%20%20%20%20End%20If%0A%20%20%20%20%0A%20%20%20%20If%20Choice%20%3D%20%22Lower%22%20Then%0A%20%20%20%20%20Target%20%3D%20StrConv(Target%2C%20vbLowerCase)%0A%20%20%20%20End%20If%0A%20%20%20%20%0A%20%20%20%20If%20Choice%20%3D%20%22Proper%22%20Then%0A%20%20%20%20%20Target%20%3D%20StrConv(Target%2C%20vbProperCase)%0A%20%20%20%20End%20If%0A%0A%20%20%20%20%20%20%20%20Application.EnableEvents%20%3D%20True%0A%20%20%20%20End%20If%0A%20%20%20%20On%20Error%20GoTo%200%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EHow%20it%20works%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CUL%3E%3CLI%3EUse%20Data%20validation%20%26amp%3B%20create%20Drop%20down%20list%20in%20cell%20F1%2C%20where%20list%20must%20comprise%20Upper%2C%20Lower%20%26amp%3B%20Proper.%3C%2FLI%3E%3CLI%3ETo%20open%20the%20VB%20editor%20either%20press%20%3CSTRONG%3EAlt%2BF11%26nbsp%3B%3C%2FSTRONG%3Eor%20Right%20click%20the%20Sheet%20Tab%20%26amp%3B%20from%20the%20menu%20hit%20View%20Code.%3C%2FLI%3E%3CLI%3ECopy%20%26amp%3B%20Paste%20this%20code.%3C%2FLI%3E%3CLI%3EReturn%20to%20the%20sheet%20%26amp%3B%20save%20the%20WB%20as%20Macro%20enable%20*.xlsm.%3C%2FLI%3E%3CLI%3ESelect%20your%20choice%20from%20Drop%20down%20in%20F1.%3C%2FLI%3E%3CLI%3EEnter%20value%20in%20range%20A1%3AA10%2C%20this%20code%20will%20convert%20the%20Case%20as%20your%20choice.%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3CSTRONG%3EN.B.%20%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CEM%3EIn%20VBA%20code%26nbsp%3BRange(%22A1%3AA10%22))%20is%20editable%20%26amp%3B%20you%20many%20set%20as%20your%20need.%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2168025%22%20slang%3D%22en-US%22%3ERe%3A%20Case%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2168025%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F561330%22%20target%3D%22_blank%22%3E%40MajorHavoc%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you're%20looking%20for%20a%20formula%2C%20you%20could%20try%3A%3C%2FP%3E%3CP%3E%3DIF(CODE(B1)%26gt%3B%3D97%2CUPPER(B1)%2CLOWER(B1))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%2C%20as%20Rajesh%20stated%2C%20if%20you%20want%20it%20to%20happen%20automatically%2C%20then%20you%20will%20need%20vba.%20Should%20it%20apply%20to%20the%20entire%20worksheet%2C%20or%20just%20particular%20cells%3F%20Another%20way%20I've%20seen%20it%20done%20is%20to%20do%20a%20bitwise%20xor%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%20%20%20%20%20Const%20ascDiff%20As%20Long%20%3D%20%26amp%3BH20%0A%20%20%20%20%20Dim%20rangeToChange%20As%20Range%0A%20%20%20%20%20Dim%20cell%20As%20Range%0A%20%20%20%20%20%0A%20%20%20%20%20Set%20rangeToChange%20%3D%20Me.Range(%22A1%3AA10%22)%0A%20%20%20%20%20%0A%20%20%20%20%20If%20Intersect(Target%2C%20rangeToChange)%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20%20%20Exit%20Sub%0A%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20%20Application.EnableEvents%20%3D%20False%0A%20%20%20%20%20%0A%20%20%20%20%20For%20Each%20cell%20In%20Intersect(Target%2C%20rangeToChange).Cells%0A%20%20%20%20%20%20%20%20%20%20If%20(Not%20Target.HasFormula)%20And%20(Len(Target.Value)%20%3D%201)%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Target%20%3D%20Chr(Asc(Target.Value)%20Xor%20ascDiff)%0A%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20Next%20cell%0A%20%20%20%20%20%0A%20%20%20%20%0AExitProc%3A%0A%20%20%20%20Application.EnableEvents%20%3D%20True%0A%20%20%20%20Exit%20Sub%0A%20%20%20%20%0AErrHandler%3A%0A%20%20%20%20MsgBox%20%22Error%20%22%20%26amp%3B%20Err.Number%20%26amp%3B%20%22%3A%20%22%20%26amp%3B%20Err.Description%0A%20%20%20%20Resume%20ExitProc%0A%20%20%20%20%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2170143%22%20slang%3D%22en-US%22%3ERe%3A%20Case%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2170143%22%20slang%3D%22en-US%22%3EOr%2C%20I%20believe%20this%20will%20work%20as%20well.%20I%20did%20not%20realize%20excel%20had%20a%20bitxor%20function%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DCHAR(BITXOR(CODE(A1)%2C32))%3C%2FLINGO-BODY%3E
New Contributor

Changing a letter to its opposite case. Input is 'A', output should be 'a'. And vice versa. Thanks.

3 Replies

@MajorHavoc 

 

Since you want to automate the exercise so that your need is VBA macro.

 

Use this VBA code as Standard module.

 

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Choice As String
Choice = Range("F1").Value

If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub

    On Error Resume Next

    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
        Application.EnableEvents = False
    
    If Choice = "Upper" Then
     Target = StrConv(Target, vbUpperCase)
    End If
    
    If Choice = "Lower" Then
     Target = StrConv(Target, vbLowerCase)
    End If
    
    If Choice = "Proper" Then
     Target = StrConv(Target, vbProperCase)
    End If

        Application.EnableEvents = True
    End If
    On Error GoTo 0
End Sub

 

How it works:

  • Use Data validation & create Drop down list in cell F1, where list must comprise Upper, Lower & Proper.
  • To open the VB editor either press Alt+F11 or Right click the Sheet Tab & from the menu hit View Code.
  • Copy & Paste this code.
  • Return to the sheet & save the WB as Macro enable *.xlsm.
  • Select your choice from Drop down in F1.
  • Enter value in range A1:A10, this code will convert the Case as your choice.

N.B.

In VBA code Range("A1:A10")) is editable & you many set as your need.

@MajorHavoc 

 

If you're looking for a formula, you could try:

=IF(CODE(B1)>=97,UPPER(B1),LOWER(B1))

 

But, as Rajesh stated, if you want it to happen automatically, then you will need vba. Should it apply to the entire worksheet, or just particular cells? Another way I've seen it done is to do a bitwise xor:

 

Private Sub Worksheet_Change(ByVal Target As Range)
     Const ascDiff As Long = &H20
     Dim rangeToChange As Range
     Dim cell As Range
     
     Set rangeToChange = Me.Range("A1:A10")
     
     If Intersect(Target, rangeToChange) Is Nothing Then
          Exit Sub
     End If
          
     Application.EnableEvents = False
     
     For Each cell In Intersect(Target, rangeToChange).Cells
          If (Not Target.HasFormula) And (Len(Target.Value) = 1) Then
               Target = Chr(Asc(Target.Value) Xor ascDiff)
          End If
     Next cell
     
    
ExitProc:
    Application.EnableEvents = True
    Exit Sub
    
ErrHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume ExitProc
    
End Sub

 

 

 

Or, I believe this will work as well. I did not realize excel had a bitxor function:

=CHAR(BITXOR(CODE(A1),32))