New Table( "Untitled",
Add Rows( 2 ),
New Column( "source text",
Character,
"Nominal",
Set Values( {"AbCd!@#GhI!!31", "aa BB CCCC"} )
),
New Column( "formula column",
Character,
"Nominal",
Formula( Lowercase( Regex( :source text, "[!@# ]+", " ", GLOBALREPLACE ) ) )
)
)
The regex means:
[ ... ] defines a set of characters, in this case, bang, at, pound, and space. Well, that's what I call them. the plus means one or more of the items in the set. The only issue is what characters in the set must be escaped with a backslash: regex - What special characters must be escaped in regular expressions? - Stack Overflow says the characters to escape inside [ ] are ^-]\ (which includes the \ escape character, of course.) If you needed a minus in the set, you could add it like "[!@# \-]+". You can escape other characters too, but it just makes it hard to read, and the ! is especially hard since it collides with JSL's escape: "[\!\!\@\#\ \-]+" (wow! \!\ is a \, then ! is itself.).
the regex matches runs of characters in the set and replaces them with a single space. GLOBALREPLACE means repeat the operation as many times as possible.
write("[\!\!\@\#\ \-]+")
[\!\@\#\ \-]+
The site I refer to for regex help is Regular Expression Tutorial - Learn How to Use Regular Expressions
Craige