"NN""/> "NN""/>
Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
miguello
Level V

Language independent text to number conversion of a form "Column NN" -> "NN"

One of my users complained that my add-in wasn't working. It was cutting off all the data for one of the variables below 100.

Upon troubleshooting I found that it is due to the way I convert "Column NN" to just number "NN". The source CSV file doesn't have column names, so when I import it, JMP assigns default names. My user has language set to Japanese so it imports CSV files naming columns as "列NN" which breaks my formula for numbers less than 100 (I was just taking the three right symbols, so numbers less than 100 were still coming out as numbers). 

I can easily fix it this case, but let's say I want to make it 100% language independent, and I don't know all the local formats in which in might imports the header-less CSV file - how would I convert something like "CCCNNCCC" to "NN" - where "CCC" - arbitrary number of chars and "NN" arbitrary number of numbers? Some RegExp magic?

 

 

3 REPLIES 3
Highlighted
txnelson
Super User

Re: Language independent text to number conversion of a form "Column NN" -> "NN"

Here is a simple function that will do the trick

getNumber = Function( {input},
	{defaultlocal},
	numStart = Length( Word( 2, z, "0123456789" ) );

	number = "";
	While( Is Missing( Num( Substr( z, numStart, 1 ) ) ) == 0,
		number = number || Substr( z, numStart, 1 );
		numStart++;
	);

	number = Num( number );
);

value = getNumber( "wd*()578pqxkkl" );

Show( value );
Jim
Highlighted
miguello
Level V

Re: Language independent text to number conversion of a form "Column NN" -> "NN"

Jim, thanks a lot.... This is the brute force solution I hoped to avoid. I don't like regular expressions because it is hard for me to understand them, but I think the way I posted below should be working just fine and is pretty simple and elegant.
Highlighted
miguello
Level V

Re: Language independent text to number conversion of a form "Column NN" -> "NN"

Ok, looks like this is going to work:

 

:numberColumn =Num( Regex(:characterColumn, "[0-9]+"));