Format Cells - Custom

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
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top