How to convert char to num

[#1]

Dear All,

I have the data as follows and whatever I tried I can't make the new formula column a numeric one.

Could someone help please?

many thanks in advance

[#2]

thank you dlee0416, could you please send the jmp data table? I am not familiar with Munger function and additional knowledge is always welcome. many thanks

[#3]

JSL Character String Functions

[#4]

Dear Craige,

Thank you for the very useful link.

I was looking forward for a long time for jsl capabilities of CTRL+F and REPLACE function. There is a line about it in your link but I couldn't get success.

I have a column (comment) with full of text and I would like to replace all exclamation marks with spaces and then collapse whitespace and make all lowercase.

what should be the jsl for:

find "'" + ! % / = ( ) : ; ? _ – ; - . , " and replace with " "

collapse whitespace

make lowercase

Many thanks in advance

[#5]

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

[#6]

I was not aware or the GLOBALREPLACE option. Thanks for pointing that out to me. Bye Regex loops.

[#7]

Yes, if GLOBALREPLACE will work in place of a for-loop, it will be much faster for several reasons: the internal loop will be faster than the JSL loop and the internal regex GLOBALREPLACE doesn't start over after each replacement and the result string is built in a single pass.  Which makes a big difference if there are a lot of replacements in a long string.

[#8]

when I fill the expression with exclamation marks JMP stops opeartiong and forces me to force quit the whole application am i doin sthg wrong?

the screenshot is where JMP freezed, dont know exactly which exclamation mark caused it...

[#9]

I think I am seeing a quotation mark inside the quoted string in the image you supplied.  JMP strings need escapes too, and it gets confusing.  Try using \!" to replace " in the string.  the apostrophe ( ' ) is OK, just the quotation mark ( " ) needs help.  I get several error dialogs when I do it wrong.  Not sure why you are seeing a hang, maybe the dialog is behind the window.

Lowercase(Regex(:source text, "[!'\!"+%/=()]+", " ", GLOBALREPLACE))

[#10]

Saitcopuroglu,

Here’s a simple table with the formula in the second column illustrating the Munger function.

Dave

[#11]

Saitcopuroglu, you can use substitute() to replace any string you want (and special characters are strings). You can have multiple substitutes "inside" each other, with each substituting a special character by a space " ", or nothing "" or whatever you like "really anything".

Then there are separate character functions to remove whitespace and turn to lowercase.

The JMP help (F1) for Character functions is really good to see what can be done; you may want to have a look.

You will find the formula of the last column pasted below the picture so you can re-use it easily. Add more Substitutes() as required to remove other special characters I have missed out.

Lowercase(Collapse Whitespace(Substitute(Substitute(Substitute(Substitute(Substitute(Substitute(:Comment, "!", " "), "+", " "), "%", " "), "_", " "), "-", " "), "?", " ")))