Format Cells - Custom

  • Thread starter Thread starter PatM
  • Start date Start date
P

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
 
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
 
Back
Top