Crystal Reports Format Field with Dashes

Hello,

I have an 11 digit field that has to be separated with dashes.  For example, 11111111111 would need to change to 11111-1111-11.

Thanks for any help,

Mike
Michael GrahamData AnalystAsked:
Who is Participating?
 
James0628Commented:
Just create a new formula in the Field Explorer and then put that formula on the report.

 James
0
 
James0628Commented:
The simplest solution is probably a formula like this:

Picture (CStr ({your field}, "00000000000"), "xxxxx-xxxx-xx")

 That converts the numeric field into an 11 character string, with leading 0's if necessary, and then uses Picture to insert the "-"s in the string.

 James
0
 
mlmccCommented:
You could try using the CStr and Replace functions

Replace(CStr(11111111111,'######,####,##'),',','-')

Another method would be

Picture(CStr(11111111111,0,''),'xxxxxx-xxxx-xx')

mlmcc
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Michael GrahamData AnalystAuthor Commented:
Thank you.  Do you know where I would put this formula?
0
 
Michael GrahamData AnalystAuthor Commented:
okay,

this ended up working for me:

Left ({vw_reporting_biannualaudit.NDC},5 )+"-"
+Mid ({vw_reporting_biannualaudit.NDC},6 ,4 )+"-"
+Right ({vw_reporting_biannualaudit.NDC},2 )


Thank you for letting me know where to put the formula
0
 
James0628Commented:
Yeah, that will work.  FWIW, if the field is a string, you could also use the Picture function that we mentioned.

Picture ({vw_reporting_biannualaudit.NDC}, "xxxxx-xxxx-xx")

 If the field is not always 11 characters long, then you might need to check for that, depending on how you wanted to handle that.

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.