One of my former students sent me a question. He wanted to know the easiest way to add the dashes back into a Social Security number when it is stored as a 9 digit string. I was going to have him parse it into 3 pieces using substring brackets and then assemble those around the dashes. Then I remembered the picture() function. I don’t use it much, but it has been on my potential blog topic list for a while.

The picture() function allows you to insert characters and punctuation into a string so that it has a specific format. The two most common uses I have seen are adding the parentheses and dash back to the US Phone number: “(000)000-0000” or adding the dashes back to a SSN: “000-00-0000”. It will work with any consistent pattern.

To use the picture function you give it two arguments. The first is the field or string you want to format and the second is a pattern string. The pattern string has an ‘x’ for every character in the field. You then insert the other characters among the ‘x’s at the appropriate places. Crystal will insert those characters automatically at those positiions.

The picture function for the Phone Number would look like this:

Picture({table.phone}, '(xxx)xxx-xxxx')

The picture function for the SSN would look like this:

Picture( {table.SocSec} , 'xxx-xx-xxxx')

Note, that if the field is empty or short the formula will still show all of the inserted characters.