cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
saitcopuroglu
Level IV

How to convert char to num

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

10199_Screen Shot 2015-10-15 at 14.35.29.png

26 REPLIES 26
saitcopuroglu
Level IV

Re: How to convert char to num

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

Craige_Hales
Super User

Re: How to convert char to num

saitcopuroglu
Level IV

Re: How to convert char to num

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

10215_Screen Shot 2015-10-19 at 10.38.25.png

Craige_Hales
Super User

Re: How to convert char to num

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
ms
Super User (Alumni) ms
Super User (Alumni)

Re: How to convert char to num

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

Craige_Hales
Super User

Re: How to convert char to num

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.

Craige
saitcopuroglu
Level IV

Re: How to convert char to num

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...

10236_Screen Shot 2015-10-19 at 23.56.51.png

Craige_Hales
Super User

Re: How to convert char to num

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))

Craige
DaveLee
Level IV

Re: How to convert char to num

Saitcopuroglu,

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

Dave

jvillaumie
Level III

Re: How to convert char to num

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.

10216_Saitcop.png

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