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.
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.