Choose Language Hide Translation Bar
Highlighted
Community Trekker

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
Highlighted
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
4 REPLIES 4
Highlighted
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
Highlighted
Community Trekker

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

Thierry R. Sornasse
Community Trekker

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?
Highlighted
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