BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
saitcopuroglu
Community Trekker

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

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
jvillaumie
Community Trekker

Re: How to convert char to num

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

10205_parite.png

26 REPLIES 26

Re: How to convert char to num

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

0 Kudos
saitcopuroglu
Community Trekker

Re: How to convert char to num

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

0 Kudos
DaveLee
Community Trekker

Re: How to convert char to num

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

0 Kudos
pmroz
Super User

Re: How to convert char to num

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

saitcopuroglu
Community Trekker

Re: How to convert char to num

diplays missing data

10200_Screen Shot 2015-10-15 at 15.04.09.png

0 Kudos

Re: How to convert char to num

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.

jvillaumie
Community Trekker

Re: How to convert char to num

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

10205_parite.png

saitcopuroglu
Community Trekker

Re: How to convert char to num

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)

0 Kudos
DaveLee
Community Trekker

Re: How to convert char to num

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