How to convert text to uppercase (or lowercase) in-place in Excel?

Question

How to convert text to uppercase (or lowercase) in-place in Excel / Office 365?

This page suggests to use the Change Case button, but there's no such button in my copy of Excel, and it does not even appear in the list of available buttons when customizing the Ribbon.

EDIT: The Change Case button appears to be in Word, not Excel. However, the question stays: is there a similar button/option in Excel?

Answer 1

One way to do this is through VBA. You don't even have to write a macro, just select all the cells you want to convert, hit Alt-F11 followed by Control-G, and type:

For Each myCell In Selection.Cells : myCell.Value = UCase(myCell.Value) : Next

This will convert all the selected cells to uppercase (note that if the cell has a formula, it will change it to its current value!)

Answer 2

This doesn't scale very well to large numbers of cells, but an in-place version of @spikey_richie's answer is:

• Start with your existing text:

  

• Edit the expression in the formula bar to surround the existing text with =upper(" and "), but don't press enter:

  

• Now press F9 to evaluate the expression, which will replace the formula in the cell with the calculated value:

  

• And then press enter to accept the value:

  

Note if there are special characters like double-quotes in the string you'll need to escape those as well, so this approach has limited usefulness, but it can be handy for a quick bodge.

The F9 key is also useful for other expressions - I frequently use =TODAY() + F9 to enter today's literal date into a cell so I don't have to look at my calendar :-).

Answer 3

The page you have linked to gives several different methods for converting case in Excel. The button in the screenshot you have pasted is actually in MS Word, not MS Excel. The guide is suggesting you copy the text from Excel to a table in Word, change case using the button on the Word ribbon bar, then copy-n-paste it back into Excel.

Answer 4

Use Excel's =Upper() and =Lower() functions.

Uppercase

Lowercase

There's also =Proper(), which outputs like this:

You can then copy values of the correctly formatted cell, over the top of the original incorrect text.

Answer 5

You can use the Automate tab to create and run Office scripts if you meet the requirements. These scripts are like macros but they are written in TypeScript and are saved to your Office account so you can reuse them in other workbooks.

Here is an example of a script that changes the case of selected cells in-place on a cell-by-cell basis.

function main(workbook: ExcelScript.Workbook) {
    
    const selectedRange = selectedSheet.getSelectedRange();
    const rowCount = selectedRange.getRowCount();
    const colCount = selectedRange.getColumnCount();

    for (let c = 0; c < colCount; c++) {
        for (let r = 0; r < rowCount; r++) {

            const cell = selectedRange.getCell(r, c);
            const text = cell.getText();
            
            if (text.length === 0) {
                continue;
            }

            const isLower = text[0] === text[0].toLowerCase();

            if (isLower) {
                cell.setValue(text.toUpperCase());
            } else {
                cell.setValue(text.toLowerCase());
            }
        }
    }
}

Answer 6

Yet another way of using VBA functions.

You can also invoke VBA functions from the Name Box. Don't need equal sign, may use arguments, references may be also in R1C1 style (local), punctuation also local. Function must return reference. For example:

Function UP(rng As Range) As Range
      Dim c As Range
      For Each c In rng
            c.FormulaLocal = UCase(c.FormulaLocal)
      Next c
      Set UP = ActiveCell
End Function

In the Name Box: UP(D3:H5), UP((A3;B5;C2)) (use local separators, press Enter)

Function UPS() As Range
   Dim c As Range
   For Each c In Selection
      c.FormulaLocal = UCase(c.FormulaLocal)
   Next c
   Set UPS = Selection
End Function

In the Name Box: UPS() (use empty brackets, press Enter) A range for conversion should be previously selected.

Answer 7

In-place in Excel? Not really, there is practically non-existent support for regex.

Luckily, copying data from Excel puts it in tab-separated format so you could use a competent text editor to achieve the results:

1. Copy your data from Excel
2. Paste into Notepad++ or anything with robust regex support like regex101.com
3. Initiate regex find+replace
   • Find: [a-z] or you can use . instead to account for non-English characters
   • Replace: \U$0
   • Alternatively, you could select all the text and use Ctrl+Shift+U
4. Replace All
5. Copy+Paste back into Excel