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
ENTHU
Level IV

Converting alphabet to digits

I have a column that contains vaalues like M N , Y Z etc.

I need to remove the space in between,swap the characters and convert last alphabet to digit.

That means -

input : M N

output : N13

 

How can I do this?

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Converting alphabet to digits

check to see if you only have 1 word and if so, then change the response

if(word(2, :Column 1, " ") == "",
     do what you want if only one letter,
    Word( 1, :Column 1, " " ) || Char(
	Hex To Number( Hex( Word( 2, :Column 1, " " ) ) ) - 64
    )
)
Jim

View solution in original post

4 REPLIES 4
txnelson
Super User

Re: Converting alphabet to digits

This will work for your displayed case....but I don't know if it is complete enough.

alphabet="ABCDEFGHIJKLMNOPQRSTUVWXYZ";
word(2,:targetvariable," ") || Char( contains( alphabet, word(1,:targetvariable," ")))

  This is a formula that can be used for a formula for a new column. 

Jim
Thierry_S
Super User

Re: Converting alphabet to digits

Hi, 

 

Try the following Formula (copy - paste in the Formula Editor)

Word( 1, :Column 1, " " ) || Char(
	Hex To Number( Hex( Word( 2, :Column 1, " " ) ) ) - 64
)

Here is a snapshot of what the table will look like 

SIMPLE TABLE.PNG

Thierry R. Sornasse
ENTHU
Level IV

Re: Converting alphabet to digits

Thanks for the response.For M N , the expected output is N13. I was able to change the Word function and get this working. There can be cases when column 1 has only one alphabet like "A". In this case i need to print A0 in column 2. How can I extend the code to do this?
txnelson
Super User

Re: Converting alphabet to digits

check to see if you only have 1 word and if so, then change the response

if(word(2, :Column 1, " ") == "",
     do what you want if only one letter,
    Word( 1, :Column 1, " " ) || Char(
	Hex To Number( Hex( Word( 2, :Column 1, " " ) ) ) - 64
    )
)
Jim