Cleaning Up Data in Excel. You’ll learn how to “clean up” data in this lesson by using the TRIM, PROPER, and CLEAN functions to remove extra spaces, non-printable characters, and fix the capitalization of words – and you’ll apply it directly to the customer due diligence file we’ve been working with.
Important: Custom number formats affect only the way a number is displayed and do not affect the underlying value of the number. Custom number formats are stored in the active workbook and are not available to new workbooks that you open. Select the cell or range of cells that you want to format. On the Home tab, next to General, in the Number Format box, click the arrow, and then click More Number Formats. In the Format Cells dialog box, under Category, click Custom. In the Type list, select the number format that you want to customize.
The code used for the number format appears in the Type box above the Type list. Important: Custom number formats affect only the way a number is displayed and do not affect the underlying value of the number. Custom number formats are stored in the active workbook and are not available to new workbooks that you open.
Select the cell or range of cells that you want to format. On the Home tab, next to Number in the Number Format box, click the arrow, and then click More Number Formats. In the Format Cells dialog box, under Category, click Custom. In the Type box, type.0 followed by the postal code format that you want to use. Note: This special number format is available only if the Location (Language) is set to United States (English). Different locales provide different or no special number formats for Social Security numbers. Display only the last few digits of Social Security numbers For common security measures, you may want to display only the last few digits of a Social Security number and replace the rest of the digits with zeros or other characters.
You can do this by using a formula that includes the CONCATENATE and RIGHT functions. This procedure assumes that the complete Social Security numbers are located in one column and that you have a second column where you will display only the last few digits of the Social Security numbers. Important: To prevent other people from viewing an entire Social Security number, you can first hide the column that contains that number, and then protect the worksheet so that unauthorized users cannot unhide the data. As a best practice, you may want to avoid storing complete Social Security numbers in your workbooks. Instead, store the full Social Security numbers in a location that meets stringent security standards (for example, a database program), and then use only the last four digits of the numbers in your workbooks.
In an empty column, click in the first cell. On the Formulas tab, click Insert Function, type TEXT, and then type CONCATENATE. In the Formula Bar, edit the formula so that it looks like this: =CONCATENATE('000-00-', RIGHT(:,4)) where is the first cell (for example, A1) and is the last cell (for example, A10) in the range of cells that contain the Social Security numbers. Excel displays the '000-00-' text string instead of the first 5 digits of the Social Security number and combines it with the last four digits of the Social Security number (for example, 0). In the cell that contains the formula, drag the fill handle down the column to populate the remaining cells in the range.
Display numbers as a phone number. Select the cell or range of cells that you want to format. On the Home tab, next to Number in the Number Format box, click the arrow, and then click More Number Formats. In the Format Cells dialog box, under Category, click Special. In the Type list, click Phone Number. Important: Custom number formats affect only the way a number is displayed and do not affect the underlying value of the number.
Custom number formats are stored in the active workbook and are not available to new workbooks that you open. Select the cell or range of cells that you want to format. On the Home tab, under Number, on the Number Format pop-up menu, click Custom.
In the Format Cells dialog box, under Category, click Custom. In the Type list, select the number format that you want to customize. The code used for the number format appears in the Type box above the Type list. Important: Custom number formats affect only the way a number is displayed and do not affect the underlying value of the number. Custom number formats are stored in the active workbook and are not available to new workbooks that you open. Select the cell or range of cells that you want to format. On the Home tab, under Number, on the Number Format pop-up menu, click Custom.
In the Format Cells dialog box, under Category, click Custom. In the Type box, type.0 followed by the postal code format that you want to use. Note: This special number format is available only if the Location (Language) is set to United States (English). Different locales provide different or no special number formats for Social Security numbers. Display only the last few digits of Social Security numbers For common security measures, you may want to display only the last few digits of a Social Security number and replace the rest of the digits with zeros or other characters.
You can do this by using a formula that includes the CONCATENATE and RIGHT functions. This procedure assumes that the complete Social Security numbers are located in one column and that you have a second column where you will display only the last few digits of the Social Security numbers. Important: To prevent other people from viewing an entire Social Security number, you can first hide the column that contains that number, and then protect the worksheet so that unauthorized users cannot unhide the data. As a best practice, you may want to avoid storing complete Social Security numbers in your workbooks.
Instead, store the full Social Security numbers in a location that meets stringent security standards (for example, a database program), and then use only the last four digits of the numbers in your workbooks. In an empty column, click in the first cell. On the Formulas tab, under Function, click Insert, point to Text, and then click CONCATENATE. In the Formula Bar, edit the formula so that it looks like this: =CONCATENATE('000-00-', RIGHT(:,4)) where is the first cell (for example, A1) and is the last cell (for example, A10) in the range of cells that contain the Social Security numbers. Excel displays the '000-00-' text string instead of the first 5 digits of the Social Security number and combines it with the last four digits of the Social Security number (for example, 0).
In the cell that contains the formula, drag the fill handle down the column to populate the remaining cells in the range. Display numbers as a phone number. Select the cell or range of cells that you want to format. On the Home tab, under Number, on the Number Format pop-up menu, click Custom.
In the Format Cells dialog box, under Category, click Special. In the Type list, click Phone Number.