Removing Punctuation from a Calculated Field

[pittelman]

Hi. I could use some help with setting up a calculated field. My question is: how can I define a calculated field to remove all punctuation?

In case it helps to know the context I am using it in:

This field is converting a person's email address into a username. The username is defined as everything that comes before the @ sign in the email, must be all lowercase and must have no punctuation. So for example, "[email protected]" would have the user name of "fred22". I've got the first two parts covered, but I can't figure out how to remove the punctuation! So here's what I have so far, which is working okay:

(Lower(LeftWords(Email, 1)

Thanks so much!

-Karen

[QuinTech]

FileMaker Pro 3! Wow, you deserve a hand for sticking with that. Can't remember the last time i've even thought of version 3... Anyhow, welcome to the forums.

Best thing i can think of is to do several nested substitute statements, accounting for all the possible characters you want stripped:

Substitute ( ( Left ( email , Position ( email , "@" , 1 , 1 ) - 1 ) , "!" , "" ) , "." , "" )

That's a lot to take in at once, but just taking it one Substitute function at a time will show you what exactly i mean:

Substitute ( "you!the.user" , "!" , "" ) = youthe.user

Substitute ( "youthe.user" , "." , "" ) = youtheuser

HTH.

Jerry