topic Re: Converting alphabet to digits in Discussions

Converting alphabet to digits

ENTHU — Fri, 27 Sep 2019 16:05:54 GMT

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?

Re: Converting alphabet to digits

txnelson — Fri, 27 Sep 2019 16:25:10 GMT

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.

Re: Converting alphabet to digits

Thierry_S — Fri, 27 Sep 2019 18:26:40 GMT

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

Re: Converting alphabet to digits

ENTHU — Fri, 27 Sep 2019 18:54:47 GMT

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?

Re: Converting alphabet to digits

txnelson — Fri, 27 Sep 2019 20:06:53 GMT

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
    )
)