How to Get Excel to Handle Social Security Numbers Properly

Share this content

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.

About David Ringstrom

About David Ringstrom

David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.


Please login or register to join the discussion.

By GrowthForce
Jun 26th 2015 01:11

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)