Convert Soc Sec Numbers to numbers

T

tygakat

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?
 
A

Alan

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?
 
D

DavidP

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
 
Top