To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips page.
Reader Wayne R. wins an autographed copy of Learn Excel 97 through 2007 from MrExcel from CFO for this question: “How do I get punctuation out of Social Security Numbers? I have to upload CSV files to the state. They want the social security numbers without the dashes, but my accounting software includes the dashes. I’ve been adding columns and doing the concatenate formula (linked in a series) but that leaves out the zeros when they are in the wrong place.”
The great thing about Excel is that there are often multiple solutions to any problem. Some solutions might introduce a problem that other solutions don’t. There are several solutions for removing the dashes from Social Security numbers.
Text to Columns, Then Concatenate
In this solution, you will select the range containing the Social Security numbers. You should make sure that the next two columns are blank; you will see why in Figure 3. However, for now, begin the process by following these three steps:
- Select Text to Columns from the Data menu.
- In Step 1, choose Delimited. Click Next.
- In Step 2, choose Other and type a dash in the Other field as shown in Figure 1 (below, left). Click Next.
In Step 3, you will see a Data Preview of all three columns. Shift-click on the heading for the third column in order to select all three columns. Choose Text from the Column Data Format box as shown in Figure 2 (below). Click Finish.
You will now end up with the Social Security number in three columns as shown in Figure 3 (below). Use a formula such as =A2&B2&C2 in order to concatenate the three segments back together without dashes. Once you have that formula, copy it and use Paste Values to convert the formula to values.
The important step was choosing Text in Step 3 of the Text to Columns wizard. This step ensures that you do not lose the leading zeroes from any segment.
Depending on your version of Excel, you have between 350 and 400 different functions available. While most people are familiar with SUM, AVERAGE, IF, and VLOOKUP, there are many more functions.
One rarely used function is SUBSTITUTE. This function will replace every occurrence of old_text with new_text. In the present example, you would use old_text of “-” and new_text of “” to remove the dashes from the Social Security number.
In Figure 4 (below, right), the formula is: =SUBSTITUTE(A1,”-”,”"). After entering the formula and copying it down to all of your rows, you will want to copy the range of formulas and Paste as Values.
Using Find and Replace
If Social Security numbers could not start with zero, this would be the easiest solution. Because of the possibility of leading zeroes, you will have to do a few extra steps.
- Select the range of Social Security numbers.
- Press Ctrl h to display the Find and Replace dialog.
- In the Find What box, type a dash.
- Leave the Replace With box empty.
- Click Replace All as shown in Figure 5 (below).
If none of the employees has a Social Security number starting with zero, then you are done. Otherwise, continue with these next five steps:
- While the range of cells is selected, press Ctrl 1 to display the Format Cells dialog. (That is Ctrl and the number one.)
- In the Format Cells dialog, choose the Number tab.
- In the Category box, choose Custom.
- Clear the characters in the Type box and type nine zeroes: 000000000.
- Click OK.
The custom number format of 000000000 will ensure that the numbers are stored with nine digits. Thus, any number that begins with a leading zero will still show the zero.
Bill Jelen is the host of MrExcel.com and the author of 32 Excel books, including Excel Gurus Gone Wild. Suggest a topic for his next column at CFO.com’s Spreadsheet Community Center (right) and if your suggestion is chosen, you’ll receive a copy of one of Jelen’s new books.