Can I remove spaces from phonenumbers?

[mroos81]

Hello everybody,

I'm creating a report in Crystal Reports 7, for export of names and phonenumbers to an SMS-application. The problem is that the phonenumbers are in my system in many different formats. A few examples:
06 51238745
06 - 51238745

What I need to do is remove the spaces from the phonenumers, because the SMS-application doesn't support those, and I'll get an error.

Can anybody tell me if and how I can remove spaces from phonenumbers in Crystal Reports? I allready tried something with a formula, but that didn't worked out too well.

Hopefully, somebody can help me.

Many thanks in advance,

Michael

 

[Reebo99]

I'm assuming that these numbers are stored as strings.....

The following would be the quickest/easiest way, but it does require prior knowledge of any additional characters.

Local StringVar SMS := "06 51 2 38 74 5";
SMS := Replace(SMS,"-",""
SMS := Replace(SMS," ",""

Replace the item in bold with your field. The above assumes that you only have either a space or a - to remove.

You could go through the whole string character by character checking whether it is a blank or numeric text and remove the items that way.......

Local StringVar SMS := "0a6 5r1 2 3--8 7te4 5ww";
Local NumberVar StopChr := Len(SMS);
Local NumberVar Counter := 0;
Local StringVar Result := "";

While Counter <> StopChr Do
(Counter := Counter +1;
If isnumeric(Mid(SMS,Counter,1)) and Mid(SMS,Counter,1) <> &quot; &quot; then
Result := Result&Mid(SMS,Counter,1);
Result;

Again, replace the item in bold with your field.

Reebo
UK

 

[mroos81]

Reebo,

Thanks for your reply.
Yes, the phonenumbers are stored as strings. The name of the database-field is REL_PERSOON.TELEFOONMOBIEL

I tried to put your examples in a formula-field, but I get an error with both. Maybe it is good to repeat that I'm working with Crystal Reports 7.

With the first example, I got the error &quot;A number, currency amount, boolean or string is expected here&quot;.
It doesn't accept the &quot;Replace&quot;-statement.

With the second example, I get the same error. Here's something wrong in the 2nd line, it doesn't accept &quot;Len(SMS)&quot;.

Hopefully you can help me solving my problems with this.

Many thanks!

Michael
NL

 

[Reebo99]

Goto:

http://support.crystaldecisions.com/communityCS/FilesAndUpdates/uflrepl.exe.asp

And download the Replace UFL.

Don't know why LEN() wouldn't work...I'm sure it's been around for ages....try replacing with length(SMS).


Reebo
UK

 

[mroos81]

Hello Reebo,

Thanks for your advice. Not sure if I actually had to download those files, but I changed the formula a little, and now it's working OK. I used your first example, and changed it this way to get it work:

Local StringVar SMS := {REL_PERSOON.TELEFOONMOBIEL};
SMS := SearchandReplace(SMS,&quot;-&quot;,&quot;&quot;, TRUE);
SMS := SearchandReplace(SMS,&quot; &quot;,&quot;&quot;, TRUE);

Your second example still isn't working. I changed &quot;LEN()&quot;, to &quot;LENGTH()&quot; and that was OK. But it gives and error in the second part of the formula. The &quot;while&quot;-statement doesn't seem to work here. I get the error &quot;The remaining text does not appear to be part of the formula&quot;.

Anyway, I got the main thing working now, and thank you very much again for your help.

Michael

 

[synapsevampire]

Since the phone numbers have spaces, likely they also have formatting issues and typos since the coder didn't use any verification on the front end.

You might also check the length of each field, strip foreign characters, perhaps even creating an exception report for ill formatted entries to kick back to your data entry folk.

-k