Alpha coding

[alliswell]

a b c d e f g h i j k l m
n o p q r s t u v w x y z

Hi everybody !
In a1 is some coding.
In b1 whenever i write "a" i shud get "n" & if i write "n" i shud get "a". For example i want to write "hello" it shud get converted to "uryyb" because above in alphabets below h is u, below e is r, below l is y and anove o is b that becomes hello when converted in coding language.

Try with formula plz

Thanks & regards to all my mrexcel team

 

[Peter_SSs]

Dear peter still no reply ?

No, I thought it reasonable to have a meal.
Please be patient, helpers may be in any time zone in the world - and are not being paid to provide you with immediate answers.

alliswell.xlsm
	A	B
1	abcdefghijklm nopqrstuvwxyz
2
3
4	hello	uryyb
5	zebra	mroen
6	hello world!	uryyb jbeyq!
Coding

Cell Formulas
Range	Formula
B4:B6	B4	=LET(s,SUBSTITUTE(A$1,CHAR(10),""),m,LEN(s)/2,c,MID(A4,SEQUENCE(LEN(A4)),1),p,FIND(c,s),CONCAT(IFERROR(MID(s,MOD(p-1,m)+1+m*(p<=m),1),c)))

 

[alliswell]

No, I thought it reasonable to have a meal.
Please be patient, helpers may be in any time zone in the world - and are not being paid to provide you with immediate answers.

Thanks peter its working. But instead of writing hello i must write uryyb in A2 column and then it must convert/decode to hello in b2. Because i will write code in column A and then it must decode it in column B

And sorry bro Leave every task and have meals first. Its important.

 

[Peter_SSs]

Thanks peter its working.

You're welcome. Glad to hear it is working.

 

[alliswell]

You're welcome. Glad to hear it is working.

Peter its working. But mirrored.
I will write code in a2 like uryyb and its result must get in b2 hello

 

[Peter_SSs]

I will write code in a2 like uryyb

Did you try doing that?

 

[alliswell]

Did you try doing that?

Yeah peter bro i tried by interchanging some formulas, but received error. Logically it wont happen, coz unaware of some LET formula & some mathematics u used is really un-understandable. You are genius but i am still learning to hatch out.

 

[Peter_SSs]

Yeah peter bro i tried by interchanging some formulas, but received error.

Not sure why you were changing formulas, all you needed to do was type uryyb into the cell where I had hello without changing tghe formula at all.

alliswell.xlsm
	A	B
1	abcdefghijklm nopqrstuvwxyz
2
3
4	uryyb	hello
Coding

Cell Formulas
Range	Formula
B4	B4	=LET(s,SUBSTITUTE(A$1,CHAR(10),""),m,LEN(s)/2,c,MID(A4,SEQUENCE(LEN(A4)),1),p,FIND(c,s),CONCAT(IFERROR(MID(s,MOD(p-1,m)+1+m*(p<=m),1),c)))

 

[lrobbo314]

This looked like a fun exercise.

This is what I came up with.

=LET(
    t,CODE(MID(A4,SEQUENCE(LEN(A4)),1)),
    f,LAMBDA(x,
        LET(
            n,IF(and(x>64,x<96),65,97),
            CHAR(MOD(x-n-13,26)+n)
        )
    ),
    REDUCE("",t,LAMBDA(s,c,s&f(c)))
)

 

[alliswell]

This looked like a fun exercise.

This is what I came up with.

=LET(
    t,CODE(MID(A4,SEQUENCE(LEN(A4)),1)),
    f,LAMBDA(x,
        LET(
            n,IF(and(x>64,x<96),65,97),
            CHAR(MOD(x-n-13,26)+n)
        )
    ),
    REDUCE("",t,LAMBDA(s,c,s&f(c)))
)

Thanks a lot irobbo. I appreciate your efforts.

And thanks petersss. Hats off.

 

[alliswell]

=LET(s,SUBSTITUTE(A$1,CHAR(10),""),m,LEN(s)/2,c,MID(A4,SEQUENCE(LEN(A4)),1),p,FIND(c,s),CONCAT(IFERROR(MID(s,MOD(p-1,m)+1+m*(p<m),1),c)))

Hi peter
Can you provide me the same formula for royal kings too plz.
royalkings
1234567890
When i type r i must get 1 and whenever i type 1 i must get r. Both ways working will be easier for me to print on labels quickly.

Thanks