Format Cells - Custom

[PatM]

I am trying to convert data I receive to a specific format. I receive social
security numbers as text, occasionally the file comes over without the dashes
between the numbers. I tried converting the text to number and then did a
custom format to include the dashes. This works however if the social has a
leading zero (0) did loses it when converted. Any suggestions?

Thanks

 

[Gary''s Student]

Import the values as Text (leading zeros will remain) in column A. In B1
enter:

=IF(MID(A1,4,1)="-",A1,LEFT(A1,3)&"-"&MID(A1,4,2)&"-"&RIGHT(A1,4))

So if the dash is there, the value is displayed. If the dash is not there,
it is inserted in the correct places. Here is A1 thru B2:

123-45-6789 123-45-6789
012345678 012-34-5678