Excel shortcuts

The 5 Best Ways to Change the Case of Text in Excel

If you work with data in Excel, you may find yourself in need of changing the case of text strings from time to time. For example, you may have a list of names that are in all uppercase, and you need to change them to proper case (the first letter in each word is uppercase, and the rest are lowercase). Or, you may have a list of items that are in mixed case, and you need to change them all to uppercase or all to lowercase. Excel provides a few different ways to change the case of text.

You can use the UPPER, LOWER, or PROPER functions to change the case of text. You can also use the Text to Columns feature to split cells by character, and then you can change the case of each part of the split cell individually. Finally, you can use a macro to change the case of text. Each of these methods is described in more detail below.

Method 1: Use the UPPER, LOWER, or PROPER Functions

The UPPER function converts all letters in a text string to uppercase. The LOWER function converts all letters in a text string to lowercase. The PROPER function converts the first letter of each word in a text string to uppercase, and any other letters to lowercase.

For example, if cell A1 contains the text "Hello world", the formulas in cells B1, C1, and D1, respectively, would return the following results:

  • =UPPER(A1) returns "HELLO WORLD"
  • =LOWER(A1) returns "hello world"
  • =PROPER(A1) returns "Hello World"

You can use the UPPER, LOWER, or PROPER function on its own, or you can nest it inside another function. For example, the following formula would return the text "HELLO WORLD" in uppercase, with the first letter of each word in lowercase:

=UPPER(LOWER(A1))

You can also use the UPPER, LOWER, or PROPER function to change the case of text in a cell that contains a formula. For example, if cell A1 contains the formula ="Hello world", the formula in cell B1 would return the text "HELLO WORLD" in uppercase:

=UPPER(A1)

Method 2: Use the Text to Columns Feature

The Text to Columns feature can be used to split cells by character, and then you can change the case of each part of the split cell individually. For example, if cell A1 contains the text "Hello world", the formulas in cells B1 and C1, respectively, would return the following results:

  • =UPPER(LEFT(A1,5)) returns "HELLO"
  • =LOWER(RIGHT(A1,5)) returns "world"

You can use the Text to Columns feature to split cells by any character, not just by space. For example, if cell A1 contains the text "Hello, world", the formulas in cells B1 and C1, respectively, would return the following results:

  • =UPPER(LEFT(A1,6)) returns "HELLO,"
  • =LOWER(RIGHT(A1,6)) returns "world"

Method 3: Use a Macro to Change the Case of Text

You can use a macro to change the case of text in a cell or range of cells. The following macro would change the case of the text in cells A1:A5 to uppercase:

Sub ChangeCaseToUpper()
' Changes the case of the text in the selected cells to uppercase.
Selection.Value = UCase(Selection.Value)
End Sub

To use this macro, select the cells that you want to change, and then run the macro. The text in the selected cells will be changed to uppercase.

You can also use a macro to change the case of text in a cell or range of cells to lowercase. The following macro would change the case of the text in cells A1:A5 to lowercase:

Sub ChangeCaseToLower()
' Changes the case of the text in the selected cells to lowercase.
Selection.Value = LCase(Selection.Value)
End Sub

To use this macro, select the cells that you want to change, and then run the macro. The text in the selected cells will be changed to lowercase.

You can also use a macro to change the case of text in a cell or range of cells to proper case. The following macro would change the case of the text in cells A1:A5 to proper case:

Sub ChangeCaseToProper()
' Changes the case of the text in the selected cells to proper case.
Selection.Value = StrConv(Selection.Value, vbProperCase)
End Sub

To use this macro, select the cells that you want to change, and then run the macro. The text in the selected cells will be changed to proper case.

Method 4: Use the Paste Special Feature

You can use the Paste Special feature to change the case of text in a cell or range of cells. For example, if cell A1 contains the text "Hello world", the formulas in cells B1 and C1, respectively, would return the following results:

  • =UPPER(A1) returns "HELLO WORLD"
  • =LOWER(A1) returns "hello world"

You can use the Paste Special feature to change the case of text in a cell or range of cells to proper case. The following steps would change the case of the text in cells A1:A5 to proper case:

  1. Select the cells that you want to change.
  2. On the Home tab, in the Clipboard group, click Copy.
  3. On the Home tab, in the Clipboard group, click Paste, and then click Paste Special.
  4. In the Paste Special dialog box, click Values, and then click OK.
  5. On the Home tab, in the Editing group, click Replace.
  6. In the Find what box, type ^([a-z])
  7. In the Replace with box, type \U\1
  8. Click Replace All.
  9. On the Home tab, in the Editing group, click Replace.
  10. In the Find what box, type ^([A-Z])
  11. In the Replace with box, type \L\1
  12. Click Replace All.

The text in the selected cells will be changed to proper case.

Method 5: Use the Format Cells Feature

You can use the Format Cells feature to change the case of text in a cell or range of cells. For example, if cell A1 contains the text "Hello world", the formulas in cells B1 and C1, respectively, would return the following results:

  • =UPPER(A1) returns "HELLO WORLD"
  • =LOWER(A1) returns "hello world"

You can use the Format Cells feature to change the case of text in a cell or range of cells to proper case. The following steps would change the case of the text in cells A1:A5 to proper case:

  1. Select the cells that you want to change.
  2. On the Home tab, in the Font group, click the small arrow next to the word "Font".
  3. In the Font dialog box, click the small arrow next to the word "Effects".
  4. In the Effects dialog box, select the "All caps" check box, and then click OK.
  5. On the Home tab, in the Font group, click the small arrow next to the word "Font".
  6. In the Font dialog box, click the small arrow next to the word "Effects".
  7. In the Effects dialog box, clear the "All caps" check box, and then click OK.

The text in the selected cells will be changed to proper case.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.