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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
James0628Connect With a Mentor Commented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
All Courses

From novice to tech pro — start learning today.