ווי צו טוישן פאַל אין עקססעל 2016, 2013 אָדער 2010

Many Excel users experience difficulties due to the inability to quickly change the case of text in worksheets. For some reason, Microsoft only added this feature to Word and left Excel without it. But this does not mean that you need to manually change the text in each cell – there are several shorter ways. Three of them will be described below.

Excel special functions

In Excel, there are functions that display text in a different case – REGULATORY(), LOWER() и prop(). The first of them translates all the text into uppercase, the second – into lowercase, the third converts only the initial letters of words into uppercase, the rest leaves in lowercase. They all operate on the same principle, therefore, using one as an example – let it be REGULATORY() – you can see how to use all three.

אַרייַן פאָרמולע

1. Create a new column next to the one you want to modify, or if it’s convenient, just use an empty column next to the table.

2. Enter an equal sign (=) followed by a function name (רעגולאַטאָרי) in the column cell next to the top-most of the editable text cells.

In brackets after the function name, write the name of the adjacent cell with text (in the screenshot below, this is cell C3). The formula will look like =PROPISN(C3).

4. קלאַפּ אַרייַן.

Cell B3 now contains the text of cell C3 in uppercase.

Copy the formula to the underlying cells of the column

Now the same formula can be applied to other cells in the column.

5. אויסקלייַבן די צעל מיט די פאָרמולע.
6. Move the cursor to the small square (fill marker), which is located at the bottom right of the cell – the cursor arrow should turn into a cross.

7. Keeping the mouse button pressed, drag the cursor down to fill in all the required cells – the formula will be copied into them.
8. מעלדונג די מויז קנעפּל.

If you need to fill all the cells of the column to the bottom edge of the table, just hover over the fill marker and double-click.

Remove helper column

Now there are two columns with the same text in the cells, but in different case. To keep only one, copy the data from the helper column, paste it into the desired column, and delete the helper.

1. אויסקלייַבן די סעלז מיט די פאָרמולע און גיט Ctrl + C.

2. Right-click on the first of the cells with the desired text in the editable column.
3. Under “paste options” select the icon די וואַלועס אין די קאָנטעקסט מעניו.

4. Right click on the helper column and select אַראָפּנעמען.
5. In the dialog box that appears, select Entire Column. 

Now everything is done.

The explanation may seem complicated. But just follow the given steps and you will see that there is nothing difficult in it.

Editing the text using Microsoft Word

If you don’t want to mess around with formulas in Excel, you can use the command to change case in Word. Let’s see how it works.

1. Select the cells you want to make changes to.
2. די אַפּלאַקיישאַנז Ctrl + C or right-click on the selected area and select קאָפּיע אין די קאָנטעקסט מעניו.

3. Open a new document in Word.
4. דרוק Ctrl + V or right-click the sheet and select אַרייַנלייגן.

Now a copy of your table is in the Word document.

5. Select those table cells where you want to change the case of the text.
6. דריקט ייקאַן רעגיסטרירן, which is located in the group קאַסע אין די קוויטל היים.
7. Select one of the five case options from the drop-down list.

You can also select text and apply שיפט + פקסנומקס until the text is correct. In this way, you can choose only three case options – upper, lower and Sentence case (in which each sentence begins with an uppercase letter, the rest of the letters are lowercase).

Now that the text in the table is in the desired form, you can simply copy it back into Excel.

Applying VBA macros

For Excel 2010 and 2013, there is another way to change text options – VBA macros. How to insert VBA code into Excel and make it work is a topic for another article. Here, only ready-made macros that can be inserted will be shown.

You can use the following macro to convert text to upper case:

Sub Uppercase()

    For Each Cell In Selection

        If Not Cell.HasFormula Then

            Cell.Value = UCase(Cell.Value)

        סוף אויב

    Next Cell

סוף סאַב

For lower case, this code will do:

Sub Lowercase()

    For Each Cell In Selection

        If Not Cell.HasFormula Then

            Cell.Value = LCase(Cell.Value)

        סוף אויב

    Next Cell

סוף סאַב

Macro to make each word start with an uppercase letter:

Sub Propercase()

    For Each Cell In Selection

        If Not Cell.HasFormula Then

            Cell.Value = _

            Application _

            .WorksheetFunction _

            .Proper(Cell.Value)

        סוף אויב

    Next Cell

סוף סאַב

Now you know how you can change the case of text in Excel. As you can see, this is not that difficult, and there is not even one way to do it – which of the above methods is better is up to you.