ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Software»
  • Office Software Suites»
  • Microsoft Office

Process to convert first letter of every word of a cell in capital letters in Microsoft Excel

Updated on June 19, 2013

Have you ever had to make entry or edit entries of names or locations in a spreadsheet which requires to keep the first letter of each of the words as capital letters. For example, if there is a long list of names of customers or employees; think the names were entered in a hurry and many of the parts of the names do not have a capital first letter. Now you have to finalize the spreadsheet and make sure the data will be stored in a proper format. It would be a tiring process to edit all name cells one by one and if it’s a long list it might be near to impossible. Here I will show you an easy way to solve this problem in seconds. Let’s get started.

Get yourself ready

To do the process we have to use a computer which has Microsoft Excel installed in it. Here I am using Microsoft Excel 2010 to show you the process. Moreover, the process has been shown on a prepared Excel file of HR department of an imaginary firm.

Steps to Process to convert first letter of every word of a cell in capital letters

Step 1: Select the Column Next to Name Column. In this case it is the Department column. Doing so, the whole column will be shown in a light blue appearance.

Step 2: Right Click on the Column and Select the Insert option from the list appeared.

Inserting Column next to Employee name
Inserting Column next to Employee name

Step 3: Type down the same Field Title as in a cell aligned of the previous column

Insert the same field name
Insert the same field name

Step 4: Select the first cell under the field.

Step 5: Click on the Formula tab from the Excel Ribbon.

Step 6: Click on the Text option from the Function Library of the Formula Toolbar.

Step 7: Select PROPER from appeared the drop down list.

Select PROPER function for the cell
Select PROPER function for the cell

Step 8: When the Function Argument box appears, Click on the first cell with the Name in previous column and Click on OK.

Selecting the cell for the function to use
Selecting the cell for the function to use

Step 9: Automatically all the parts of the Name will have capital letter at the beginning. Now Double Click on the Lower Left Corner of the cell to execute the function in all the following cells of the range.

Double click on lower left corner to execute the function in that range
Double click on lower left corner to execute the function in that range

Step 10: Now, Select all the cells you just have executed the formula on and then Copy them.

Copy all the cells where the function has been executed
Copy all the cells where the function has been executed

Step 11: Right Click on the same range and Select Paste Value from the Paste Special option.

Paste Value on the same selected cells
Paste Value on the same selected cells

Step 12: Now Right Click on the previous column with the improper name format and then Select Delete option from the Menu.

Delete the Previous Column with improper name format
Delete the Previous Column with improper name format

How the process works

The PROPER function is the function to convert the first letter of every word of the reference cells to capital letters. Moreover, in the last steps- where we copied and pasted the value in the cells, we smartly replaced the result of the function only with the output values, so when we deleted the previous column- it did not affect the data of the new column at all.

Think More

Do not delete the previous column before you copy and paste the value in the new column. Otherwise the whole new column will be useless.

Comments

    0 of 8192 characters used
    Post Comment