How to Get Excel to Handle Social Security Numbers Properly

istock_kitzcorner_excel.jpg

istock_kitzcorner_excel
istock_kitzcorner_excel
2

During the recent High Impact Excel: VLOOKUP Edition webinar a senior financial analyst named Lisa asked a question related to Social Security numbers. She periodically receives employee lists where the Social Security numbers aren’t necessarily in a uniform format. For instance, some are all numeric, others have dashes, and some might be text-based. Any data analysis involving look-up functions in Excel requires that our data be clean. Here are some techniques for cleaning up the numbers.

The first step in cleaning up data involves eliminating extraneous characters, such as dashes. An easy way to do so is to select the column of data, and then press Ctrl-H to display the Replace dialog box in Excel. As shown in Figure 1, enter a dash in the Find What field, and then leave the Replace With field blank. Make sure you’ve selected a single column of data, and then click Replace All.

Figure 1: The Replace dialog box makes it easy to eliminate dashes from text-based Social Security numbers.

Please Login or Register to read the full article

To access all of the content on our site, register (it's free!) or login to your existing account.

Replies

Please login or register to join the discussion.

These are all great tips, especially for HR and Payroll professionals who have to manage and sort through multiple employee information on a regular basis. Just make sure your excel files are secured safely and backed-up to make sure that you have tabs on your employee data at all times!

Thanks (0)

Thank you for your feedback, and great additions to my article!

Thanks (0)