Microsoft Excel provides the user with several functions to convert the format of text already entered into a worksheet. In Excel 2013 it is possible to directly change a series of proper names entered in lowercase, for example by transforming the initials to uppercase, using the "Preview suggestions" functionality without having to rewrite them. If you need to capitalize an entire text, you can use the SHIFT function or the SHIFT START function to capitalize only the first letter of each word.
Steps
Method 1 of 4: Use the SHIFT Function
Step 1. Type a series of names or plain text into a column of the worksheet
When using this function, the text can consist of either uppercase or lowercase characters. Using the "SHIFT" function of Excel, the text in the selected cell will become entirely uppercase.
Step 2. Add a new column to the right of the one that contains the text to be edited
Select the header of the column containing the data with the right mouse button, then choose "Insert" from the context menu that appeared.
Step 3. Move the text cursor inside the first cell to the right of the one containing the data to be changed to uppercase
Inside this cell you need to insert the formula that uses the "SHIFT" function.
Step 4. Press the "Insert Function" button located in the toolbar
It is labeled "fx". By pressing the button in question, the bar for inserting the formulas will be highlighted allowing you to type in the desired function.
Step 5. Choose the function called "SHIFT" or type the keyword "SHIFT" immediately after the "=" symbol placed inside the bar relating to the composition of the formulas
In Excel, the insertion of formulas can also be enabled by pressing the "AutoSum" button characterized by the Greek letter epsilon (similar to a stylized E). In this case, the word "SUM" relating to the "Sum" function will automatically appear in the bar for entering the functions. If so, simply replace the "SUM" keyword with "SHIFT"
Step 6. Specify the name of the cell that contains the text to be edited inside the round brackets following the keyword "SHIFT"
If the data is stored within the first column and first row of the worksheet, the formula in the bar should be as follows: "= SHIFT (A1)".
Step 7. Press the "Enter" key
The text stored in cell "A1" should appear entirely in uppercase in cell "B1".
Step 8. Place the mouse pointer on cell "B1", then click the small square that appeared in the lower right corner
Drag the pointer to the end of the data series to be edited without releasing the left mouse button. The formula used in the selected cell will be automatically copied and adapted to the underlying ones, so that the contents of each cell of the first column will be copied to the corresponding one of the second column after being transformed into uppercase.
Step 9. Check that all the text has been copied correctly into the respective cells of the second column
Select the entire column that contains the text in the correct format by clicking the letter in the header. Access the "Edit" menu, then select the "Copy" item. At this point, access the drop-down menu of the "Paste" button located on the toolbar, then select the "Paste Values" option.
This step allows you to replace the formulas present in the cells of the second column with their respective values, so as to be able to delete the first column that contains the original data
Step 10. Check that the data appearing in the second column is identical to the previous ones
At this point, you can safely delete the first column of the worksheet by clicking on the respective header with the right mouse button and choosing the "Delete" option from the drop-down menu that appeared.
Method 2 of 4: Use the SHIFT START function
Step 1. Enter the data to be processed in the first column of the worksheet
The Excel function under consideration transforms the first letter of each word that makes up the text in each cell to which it is applied to uppercase.
Step 2. Add a new column
Select the header of the first column with the right mouse button, then choose "Insert" from the context menu that appeared.
Step 3. Move the text cursor to the cell to the right of the cell that contains the first element of the data series to be edited
Press the button for inserting Excel functions. It is characterized by the letters "fx" and is located directly on the Excel toolbar.
Step 4. Click inside the formula bar
This is a text bar located next to the "fx" label located at the top of the worksheet. Type the keyword "SHIFT. INIZ" immediately after the "=" symbol.
In Excel, the insertion of formulas can also be enabled by pressing the "AutoSum" button characterized by the Greek letter epsilon (similar to a stylized E). In this case, the word "SUM" relative to the "Sum" function will automatically appear in the bar for inserting the special Excel functions. If so, you simply need to replace the "SUM" keyword with "START SHIFT"
Step 5. Specify the name of the cell that contains the text to be edited inside the round brackets following the keyword "SHIFT. INIT"
In our example, the formula inside the bar should be the following: "= SHIFT START (A1)"
Step 6. Press the "Enter" key
The text stored in cell "A1" should appear in cell "B1" with the initial letter of each word uppercase while the others will be lowercase.
Step 7. Place the mouse pointer on cell "B1", then click the small square that appeared in the lower right corner
Drag the pointer to the end of the data series to be edited in the first column, without releasing the left mouse button. Release it only when all the text present in the cells of column "A" has been copied into those of column "B" and is displayed completely in lowercase with the first letter of each word in uppercase.
Step 8. Select entirely the second column, the one that presents the modified text, by clicking its header
Access the "Edit" menu, then choose the "Copy" option. At this point, access the drop-down menu of the "Paste" button located on the toolbar, then select the "Paste values" option.
The formula used to modify the text present in the original cells will be replaced with the respective values allowing you to totally delete the first column of the worksheet
Step 9. Select the first column header with the right mouse button, then choose the "Delete" option
In this way, only the values modified according to the needs of use will remain.
Method 3 of 4: Use the Preview Tips feature in Excel 2013
Step 1. This Excel feature can be used when the data to be manipulated is represented by proper names
In order to use this method, the data must be entered in the Excel worksheet in lowercase. The "Preview suggestions" function is able to recognize proper names and to capitalize the first letter of each first and last name in a completely automatic way.
Step 2. Complete entering all names making sure you use only lowercase characters
Enter all the names in a single column remembering to leave a blank one on the right of the data list.
If the column adjacent to the one containing the data is already occupied, click the header of the first column with the right mouse button, then choose the "Insert" option from the context menu that appeared. This will add a new column to the right of the selected one
Step 3. Click the cell located to the right of the first name in the list
For example, assuming that the data to be manipulated starts from cell "A1", you will need to select cell "B1".
Step 4. Type the same name in cell "A1", being careful to use the first letters of the name and surname in capital letters
For example, if the first name is "paolo rossi", in the cell on the right you will have to write "Paolo Rossi". When finished, press the "Enter" key.
Step 5. Access the "Data" menu, then choose the "Preview suggestions" item
Excel will automatically detect the schema used to modify the data and apply it to the remaining ones. You can also use the hot key combination "Ctrl + E" to activate the "Preview Tips" feature.
Step 6. Delete the column that contains the original data
To prevent the values from being duplicated, click the header of the first column (the one that contains the original name series) with the right mouse button, then choose the "Delete" option from the context menu that appeared. This will leave only the column where the correctly written names are present.
Before clearing the original dataset, make sure the "Preview Suggestions" feature has been applied correctly to the entire list of names
Method 4 of 4: Use Microsoft Word
Step 1. This is an alternative method to using Excel formulas to change the letters of a text, from upper case to lower case or vice versa, and it is very simple and fast:
Step 2. Launch Microsoft Word, then create a new blank document
Step 3. Select all the cells of the Excel worksheet which contain the text to edit
Step 4. Copy the contents of the cells (you can do this by using the key combination "Ctrl + C")
Step 5. Paste it inside the blank Word document (you can do this by using the key combination "Ctrl + V")
Step 6. Select all the text present in the Word document
Step 7. Access the drop-down menu of the "Upper / Lower Case" (Aa) button located within the "Font" group of the "Home" tab
Step 8. Choose the option that best suits your needs:
"Normal", "All lowercase", "All uppercase", "All initials uppercase" and "Reverse uppercase / lowercase".