Removing spaces and special characters from field

[arodri]

Hello,

I am using Crystal Reports XI Release 2 and I have a report that's going to be exported to excel, then saved as a CSV. It's a pretty simple report - it comes from a SQL Server table, and all I have are page header titles, and all of the data is in the details section. There are two fields, Email and LicensePlate that I need to alter only if they meet certain criteria, but I'm not sure what the formula would be.

Email field: I need to remove any spaces that appear in the email. So if for example an email came across as: m smith@gmail.com, the formula would make it: msmith@gmail.com

LicencePlate field: I need to remove ANY special characters from this field (I'm thinking a long IF statement, maybe?). So if a license plate comes across as: 3847~TX or 3847*TX then it would need to be changed to: 3847TX.

Thanks in advance for any help you guys can provide! I really appreciate it.

 

[briangriffin]

Email: replace({yourfield}," ","")


License Plate: Loop through the string and determine if it's a letter or number based on the ASCII value:

whileprintingrecords;
stringvar x;
numbervar y;
for y := 1 to len({yourfield})

do(
if ascw({yourfield}[y]) in [48 to 57,65 to 90,97 to 122]
then x := x + {yourfield}[y]
else x
);
x

 

[lbass]

You can use the replace function for each:

replace({table.email}," ","") //to remove spaces

You can nest replaces like this for the license plate:

replace(replace(replace({table.licenseplate},"~",""),"*",""),"!","")

To be really sure you could use the following for license plate, which would pick up only letters and numbers. If you want to keep letters that are lower case but should be upper case, then in file->report options check "database server is case insensitive".

stringvar x := {table.licenseplate};
stringvar y;
numbervar i;
numbervar j := len(x);
for i := 1 to j do(
if x in [chr(48) to chr(67),chr(65) to chr(90)] then
y := y + x
);
ucase

-LB

 

[lbass]

Sorry, Brian--I didn't see your post.

-LB

 

[arodri]

This was great! Thank you both so much for your help!