Convert Soc Sec Numbers to numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a file that has social security numbers input 2 ways. Some are input as 555555555 and some are input as 555-55-5555. They look the same when they are formatted as social security numbers but when I do a vlookup to match with another file that has the numbers inputted as 555555555, it gives me an error message. I don't want to have to go through hundreds of numbers to delete the dashes just so they will match. Any suggestions?
 
Can be done quite easily with SUBSTITUTE, LEFT, MID, RIGHT formulas, but I
would think the easiest way is to highlight the data, Edit > Replace>
Replace What "-" (no quotes), Replace with, leave empty, Replace All

tygakat said:
I have a file that has social security numbers input 2 ways. Some are
Click to expand...
input as 555555555 and some are input as 555-55-5555. They look the same
when they are formatted as social security numbers but when I do a vlookup
to match with another file that has the numbers inputted as 555555555, it
gives me an error message. I don't want to have to go through hundreds of
numbers to delete the dashes just so they will match. Any suggestions?
 
It should be fairly easy to eliminate the dashes. First use
autofilter to see only text that "contains" "-". I assume your data
starts in cell A1. You should now use a formula such as
=left(A1,3) & mid(A1,5,2) & right(A1,4)
Copy this down as far as necessary. Then do Copy/Paste Special Values
to convert the formulas to required values
Remove the autofilter and the job is done

David P
 
Back
Top