Is there a keyboard shortcut to change ALL CAPS to All Caps?

[JenniferMurphy]

One website that I follow has the annoying preference for ALL CAPS. I like to copy some of the lists to either a Word document or an Excel worksheet. In Word, the keyboard shortcut Shift+F3 will cycle the text between all upper case, all lower case, or one or two versions of first letter capitalization. (a) If the text ends with a period, then it assumes that it is a sentence and it will capitalize the just first letter of the first word. (b) Otherwise, it will capitalize the first letter of every word.

Here is an example:

Shift+F3 doesn't seem to work for me in Excel. Is there a way to activate it?

It would be even better if there were a fourth step for titles, where it would capitalize just the words that are normally capitalized on titles. Like this:

Is there something like this in Excel? Shift+F3 lets me enter a formula. If so, how do I use it. If not, can I add it?

Thanks

 

[shaowu459]

for your reference.

工作簿1
	A	B
1	this is some sample text with a period.	THIS IS SOME SAMPLE TEXT WITH A PERIOD.
2		this is some sample text with a period.
3		This Is Some Sample Text With A Period.
4
5
6
Sheet1

Cell Formulas
Range	Formula
B1	B1	=UPPER(A1)
B2	B2	=LOWER(A1)
B3	B3	=PROPER(A1)

 

[JenniferMurphy]

for your reference.

I didn't know about Proper and I can't imagine when I would ever use it. What I need is an Appropriate function that will do what is illustrayed in the last row. If it's a sentence, then capitalize just the first letter of the first word. If it's not a sentence, then capitalize it as a title.

Records Log.xlsx
	F	G	H
5	Original	this is some sample text with a period.	this is some sample text without a period
6	=UPPER()	THIS IS SOME SAMPLE TEXT WITH A PERIOD.	THIS IS SOME SAMPLE TEXT WITHOUT A PERIOD
7	=LOWER()	this is some sample text with a period.	this is some sample text without a period
8	=PROPER()	This Is Some Sample Text With A Period.	This Is Some Sample Text Without A Period
9	=APPROPRIATE()	This is some sample text with a period.	This is Some Sample Text without a Period
Lab Results

Cell Formulas
Range	Formula
G6:H6	G6	=UPPER(G5)
G7:H7	G7	=LOWER(G5)
G8:H8	G8	=PROPER(G5)

But I really don't want a function at all. I was asking if there is a keyboard shortcut, like we have in Word, that I can use to convert text in selected cells.

What would be really helpful is a new option based on the text format type (@). I see that "@/x" is currently invalid. So let @/l = lowercase, @/u = upper case, @/p = proper case, and @/a = my appropriate case.

 

[bobsan42]

Well, Excel is not Word. It's a completely different animal with a very different purpose in life.
There is obviously a particular way to use a certain software.
There is no such shortcut in Excel. I believe this particular shortcut also works in Outlook, maybe in PowerPoint too.
But you can always copy text to word, change case, paste back (6 or 7 consecutive shortcuts )

 

[shaowu459]

Sorry. As far as I know, there is no such shortcut key in Excel.

 

[bobsan42]

Of course one can always write a macro and assign it a shortcut to run.

 

[Perpa]

Jennifer,
Here is a macro that runs the 'Upper', 'Proper', or Lower' functions whichever you select in the Input Box.
You can run the macro using Alt+F8 and then selecting the macro 'ALL_CAPS' and then Run...Or you can create
a shortcut key (Cntl+u or whatever letter or symbol you choose). I included a link on how to set up
a shortcut key. Putting the macro in your Personal Workbook allows you to run it no matter which workbook
you are working in so you don't have to copy the macro to a new workbook each time.

How to set up a keyboard shortcut to run a macro

Note: The macro uses a helper cell in the same row as the Activecell to run the function equations for
''Upper', 'Proper', or Lower'. To run the macro:
Select a cell you want to convert, then run the macro (Alt+F8 OR Cntl+u or whatever). I set it up so the helper cell was 15
columns right of the selected cell (out of the field of view). You can change that if there is data in that location.
As the macro finishes it clears the helper cell.
I hope this is helpful.
Perpa

Sub ALL_CAPS()
Dim MyChoice As String
Dim Reply As Integer

Retry:
MyChoice = Application.InputBox("Enter 'A' for ALL CAPS, OR 'P' for Proper, OR 'L' for Lower Case", A Or P Or L)

If MyChoice = "A" Or MyChoice = "a" Then    'allows upper or lower case selection
    MyChoice ="Upper"
    GoTo Passem
End If

If MyChoice = "P" Or MyChoice = "p" Then    'allows upper or lower case selection
    MyChoice = "Proper"
    GoTo Passem
End If

If MyChoice = "L" Or MyChoice = "l" Then    'allows upper or lower case selection
    MyChoice = "Lower"
    GoTo Passem
End If

If MyChoice <> "A,a,P,p,L,l" Then
    Reply = MsgBox("ALL CAPS, Proper or Lower was not selected", vbRetryCancel, "Try again?")
    If Reply = vbCancel Then Exit Sub
End If

GoTo Retry:

Passem:
    ActiveCell.Offset(0, 15).Value = "=" & MyChoice & "(" & ActiveCell.Address & ")"    'Change if necessary
    ActiveCell.Value = ActiveCell.Offset(0, 15).Value
    ActiveCell.Offset(0, 15).Clear    'Change also if you changed the above Offset
    ActiveCell.Activate
End Sub

 

[bobsan42]

I decided to modify [B]Perpa[/B]'s solution to work on all selected cells and not to use a cell on the sheet.
Although I personally would make a separate procedure for each case and eliminate the Input request. However this would require 3 separate shortcut key combinations.
Ctrl+Q seems to be one good option for a shortcut key combination.

Sub changeCaseALP()
    'Ctrl+Q seems to be a good option for a shortcut key combination
    Dim MyChoice As String
    Dim Reply As VbMsgBoxResult, cc As Range, x As String
    
Retry:
    MyChoice = LCase(Application.InputBox("'A' for ALL CAPS" & vbLf & _
                                          "'P' for Proper Case" & vbLf & _
                                          "'L' for Lower Case", "Select a changeCase option", _
                                          , , , , , 2)) 'allows upper or lower case selection
    
    If MyChoice = "a" Then
        MyChoice = "allcaps"
    ElseIf MyChoice = "p" Then
        MyChoice = "proper"
    ElseIf MyChoice = "l" Then
        MyChoice = "lower"
    Else
        Reply = MsgBox("ALL CAPS, Proper or Lower was not selected", vbRetryCancel, "Try again?")
        If Reply = vbCancel Then Exit Sub
    End If
    
    For Each cc In Selection.Cells
        x = cc.Formula
        If x <> "" And Left(x, 1) <> "=" Then
            cc.Value = reCase(x, MyChoice)
        End If
    Next cc
    Set cc = Nothing
End Sub

Private Function reCase(ByRef x As String, ByVal MyChoice As String) As String
    
    If MyChoice = "allcaps" Then
        reCase = UCase$(x)
    ElseIf MyChoice = "proper" Then     'allows upper or lower case selection
         reCase = Application.WorksheetFunction.Proper(x)
    ElseIf MyChoice = "lower" Then    'allows upper or lower case selection
        reCase = LCase$(x)
    End If
End Function

to assign a key combination to run changeCaseALP():
put the code in a standard code module.
go back to excel application. press Alt+F8.
in the list of available macros select changeCaseALP and press Options... button

in the shortcut key box type the letter to combine with CTRL as a shortcut key combination.

Press OK and then close the Run Macro dialog.

 

[Georgiboy]

Here is a sub that will loop through four options: UPPER, LOWER, (First letter capitalised and first letter after a ". "), PROPER.
This code could also be assigned to a shortcut if you wish to do so.
Each time you run the code it will produce one of the different options above, it will do this on the selected range of cells.

Option Explicit

Dim rNum As Integer

Sub ChangeTextType()
    Dim rng As Range, rCell As Range, var As Variant, x As Long
   
    Set rng = Selection
   
    Select Case rNum
        Case 0
            rng = Evaluate("UPPER(" & rng.Address & ")")
        Case 1
            rng = Evaluate("LOWER(" & rng.Address & ")")
        Case 2
            For Each rCell In rng
                var = Split(rCell, ". ")
                For x = 0 To UBound(var)
                    var(x) = UCase(Left(var(x), 1)) & Right(var(x), Len(var(x)) - 1)
                Next x
                rCell = Join(var, ". ")
            Next rCell
        Case 3
            rng = Evaluate("PROPER(" & rng.Address & ")")
    End Select
    If rNum < 3 Then rNum = rNum + 1 Else rNum = 0
End Sub

The trouble with the "=APPROPRIATE()" idea is that it is open to interpretation which means that it is less than ideal to code, this is because you would need to list all of the words that you believe need to be capitalised OR list the words that are not to be capitalised (the latter here probably being a shorter list).
If you had a list of all of the words not to be capitalised, you could probably use PROPER to make every word capitalised and then use replace with all of the words in your (not to capitalise) list.

 

[myall_blues]

Here is some code for a user defined function that works not exactly like your APPROPRIATE, because as @Georgiboy points out you can't really know which words to capitalise and which not, but it gets most of the way there. It also only assumes a single sentence as the input string.

Book1
	A	B
1	this is some sample text with a period.	This is some sample text with a period.
2	this is some sample text without a period	This Is Some Sample Text Without A Period
3	This is some sample text with a period.	This is some sample text with a period.
4	This Is Some Sample Text Without A Period	This Is Some Sample Text Without A Period
5	THIS IS SOME SAMPLE TEXT WITH A PERIOD.	This is some sample text with a period.
6	THIS IS SOME SAMPLE TEXT WITHOUT A PERIOD	This Is Some Sample Text Without A Period
Sheet2

Cell Formulas
Range	Formula
B1:B6	B1	=appropriate(A1)

Usage: =APPROPRIATE(input text, [convert])
Input text is the text you want converted and can be a cell address.
Convert is an optional parameter as follows:
0 or not provided - converts according to your APPROPRIATE function
1 = converts to lowercase
2 = converts to proper case
3 = converts to upper case.

You could add this code to your personal macro workbook and it would be available everywhere.

Regards

Murray

Option Explicit
Function APPROPRIATE(inText As String, Optional convert As Integer)
    Dim period As Boolean
    Dim firstLetter As String
    Dim inLen As Long
    If IsMissing(convert) Then
        convert = 0
    End If
    If Right(Trim(inText), 1) = "." Then
        period = True
    End If
    inLen = Len(inText)
    Select Case True
        Case convert = 0 And period = True
            firstLetter = StrConv(Left(Trim(inText), 1), vbUpperCase)
            APPROPRIATE = firstLetter & StrConv(Mid(Trim(inText), 2, inLen), vbLowerCase)
        Case convert = 0 And period = False
            APPROPRIATE = StrConv(Trim(inText), vbProperCase)
        Case convert = 1
            APPROPRIATE = StrConv(Trim(inText), vbLowerCase)
        Case convert = 2
            APPROPRIATE = StrConv(Trim(inText), vbProperCase)
        Case convert = 3
            APPROPRIATE = StrConv(Trim(inText), vbUpperCase)
    End Select
End Function