Format SSN Numbers in Excel and Google Sheets
Written by
Reviewed by
This tutorial will demonstrate how to format SSN numbers in Excel and Google Sheets.
Social Security numbers (SSNs) can cause problems in data depending on their format. Some may be formatted as text with leading zeros, some may contain dashes and others may have the leading zeros removed as they may be formatted as a number. To work with SSN’s in Excel, we need them to have a standard format to ensure that they all have the required 9 digits. In order to achieve this, we can combine a number of Excel function to standardise and clean our SSN numbers.
SUBSTITUE Function
This function removes any dashes that you may find in the data.
As you can see in the graphic above, there was no effect on the first 2 rows of data as they did not contain dashes at all. However, the dashes in row 5 were removed from the data.
VALUE Function
The VALUE function will extract all the number out of our data. We can combine the VALUE function with the SUBSTITUTE function to make sure we extract all the values.
LEN Function
The LEN function allows Excel to tell us how many characters there are in a cell. As we need the SSN to have exactly 9 characters, once we have extracted the value from the data using the VALUE and SUBSTITUE functions, we can find out how long the result is using the LEN function.
REPT Function
The REPT function will repeat text within a formula for a set number of times as directed by the formula. By combining the functions above, and then using the REPT function to add leading zeros to the values obtained above to ensure that each of the SSN numbers has 9 characters, we end up with our perfectly formatted SSN numbers!
Format SSN Numbers in Google Sheets
The formulas to format an SNN number in Google Sheets are exactly the same as the formulas used in Excel.
