topic Re: How to convert char to num in Discussions

How to convert char to num

saitcopuroglu — Wed, 19 Oct 2016 00:55:57 GMT

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

Re: How to convert char to num

Bill_Worley — Thu, 15 Oct 2015 11:58:01 GMT

Saitcopuroglu,

You need to take the "USD" out of the data in the rows. JMP sees this as character data and will not allow you to change to numeric. You will need to a Find and replace or a Recode to try and make the change a little easier on yourself with over 1400 rows.

Best,

Bill

Re: How to convert char to num

DaveLee — Thu, 15 Oct 2015 11:58:49 GMT

I believe that all you need to do is use the 'Num' command in front of your 'Substr' command and it Neill convert to a numeric:

Num(Substr( your column here, 1,7))

Hope this fixes your problem.

Dave

Re: How to convert char to num

pmroz — Thu, 15 Oct 2015 12:00:17 GMT

Surround what you have with num(). That will convert a string to a number.

Re: How to convert char to num

saitcopuroglu — Thu, 15 Oct 2015 12:00:28 GMT

Thank you, I did it already bu substracting the first 7 digits (which are supposed to be numbers) but cant change the new formula column from char to num

Re: How to convert char to num

saitcopuroglu — Wed, 19 Oct 2016 00:56:07 GMT

diplays missing data

Re: How to convert char to num

martindemel — Thu, 15 Oct 2015 12:06:54 GMT

And if you want to add the currency, just go to column info and add to column property "Units" the value "USD", you will get the Units in brackets after the column Name.

Re: How to convert char to num

jvillaumie — Wed, 19 Oct 2016 00:56:48 GMT

Your decimal place separator is a comma, you may need to change it to a full stop for num() to work. I have to, being in the UK. Your local preferences may allow you to work with the comma.

Also, using substr() will work only if all your parity values have the same number of digits. If the number of digits increases, it will crop the value, and if the number of digits decreases, the substr() will keep some of the USD characters and num() won't work at all. I suggest you use substitute() instead of substr() to replace "USD" by nothing, and replace "," by "."

Re: How to convert char to num

saitcopuroglu — Thu, 15 Oct 2015 14:09:49 GMT

Exactly!

Only changing all the commas to full stops solved the problem on its own with substract function but with substitute function it is safer in case shorter than 5 decimals displayed data (1.50000 »» 1.5).

Many many thanks to all input owners for your support ! (And I will change my local preferences from UK to US)

Re: How to convert char to num

DaveLee — Thu, 15 Oct 2015 14:52:25 GMT

Not to beat a dead horse but you can still use the Num(Substr(….) option by doing the following.

Re: How to convert char to num

saitcopuroglu — Fri, 16 Oct 2015 06:39:15 GMT

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

Re: How to convert char to num

Craige_Hales — Wed, 09 Nov 2016 10:04:21 GMT

JSL Character String Functions

Re: How to convert char to num

DaveLee — Fri, 16 Oct 2015 13:31:26 GMT

Saitcopuroglu,

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

Dave

Re: How to convert char to num

saitcopuroglu — Wed, 19 Oct 2016 00:59:07 GMT

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

Re: How to convert char to num

jvillaumie — Wed, 19 Oct 2016 00:59:17 GMT

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, "!", " "), "+", " "), "%", " "), "_", " "), "-", " "), "?", " ")))

Re: How to convert char to num

ms — Mon, 19 Oct 2015 10:07:25 GMT

To get a slightly simpler formula, use multiple arguments in Substitute().

For example:

Substitute(:Comment, "!", " ", "+", " ", "%", " ", "_", " ", "-", " ", "?", " ");

Re: How to convert char to num

saitcopuroglu — Mon, 19 Oct 2015 11:19:41 GMT

Thank you MS,

Thank you jvillaumie,

Both work very well !

I am continuously checking the tutorials, books, help, webcasts but sometimes when it comes to action some very simple thing is missing. In this example I knew the Substitute function but did not know it could be surrounded by another Substitute and another and another and by collapse whitespace and lowercase functions...

Many many thanks again, it saved me 8 minutes for each day of my rest life...

Re: How to convert char to num

Craige_Hales — Mon, 19 Oct 2015 20:33:07 GMT

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

Re: How to convert char to num

ms — Tue, 20 Oct 2015 00:58:39 GMT

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

Re: How to convert char to num

Craige_Hales — Tue, 20 Oct 2015 17:08:23 GMT

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.