Crystal Reports phone numer

Question

I'm working in a crystal report and need to format a phone number field. The data I am working with is somewhat messed up and there are some numbers formatted correctly

[phone]

and some 1111111111

I'm trying to write this formula that will remove the parentheses and re-format the string. Here's what I have so far and have no clue why this won't work

StringVar phone = Replace({AssessorTrainingReport;1.Phone1},"(",""); 
phone := Replace({AssessorTrainingReport;1.Phone1},")","");
Picture (CStr (phone), "(xxx) xxx-xxxx");

Answer 1

The second replace call should operate on the stringvar result from the first replace call...

phone := Replace(phone,")","");

also, do you need to remove the - too?

Answer 2

// try this, in this case it allows you to replace for anything you need it to be.

    StringVar phone:= {yourTable.field};
    phone:= Replace(phone,"(","*");
    mid(phone, 1);
    replace(mid(phone, 1),")", "* "); 

Answer 3

TRY THIS...

RIGHT CLICK ON PHONE NUMBER FIELD THEN SELECT FORMAT FIELD FROM FORMAT FIELD SELECT COMMON TAB. UNDER DISPLAY STRING CLICK X-2 BOX THEN WITE FORMULA -> PICTURE({YOURTABLE.FIELD), "(XXX) XXX-XXXX")

HOPE THIS HELPS!

Answer 4

If your numbers can "be messed up" then you might want to consider sanitizing the entire string (also for those curious how to accomplish this in CR):

local stringvar phonenum := {table.phone_field};
local stringvar out := "";
local numbervar i;
for i:=1 to length(phonenum) do
  (
    if isnumeric(phonenum[i]) then //iterate over the characters in the string...
       out:=out + phonenum[i]; //...and toss out those which are non-numeric
  );

if length(out)&lt&gt10 then "Handle the error condition" else picture(out,"(xxx) xxx-xxxx")

Answer 5

Create a custom function to 'supplement' the Replace() function:

// ----------------------------------------------------------------------
// ReplaceEx()
// Author:      Craig Buchanan
// Purpose:     Support an array of find tokens
// Parameters:  text - the string being searched
//              find - an array of characters to be found
//              replacement - value to substitute
// ----------------------------------------------------------------------
Function (Stringvar text, Stringvar Array find, Stringvar replacement)

    local numbervar i;

    For i:=1 To Ubound(find) do (

        text:=Replace(text, find[i], replacement)

    );

    text;

Use custom function in a formula field:

// returns {612) 555-1212
Picture(ReplaceEx("612-555-1212", ["(",")","-"], ""), "(xxx) xxx-xxxx")