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]

In 2007 or 365/2021 as you ended up choosing in this similar thread?

 

[alliswell]

In 2007 or 365/2021 as you ended up choosing in this similar thread?

No peter its not same. Coz in this if i type upper side characters i shud get lower side one and if i type lower side characters it shud get upper side one. This i have to use to generate product secret codes. Thanks

 

[bebo021999]

Or, VBA solution.
With abc...xyz in A1, try to type something in B1 (alpha textstring)

How?
Right click in tab's name, viewcode, then paste below code into:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i&, n&, pos&, OriginText As String, oldText As String, st As String, newText As String
If Target.Column <> 2 Then Exit Sub
OriginText = LCase(Target.Offset(0, -1).Value)
n = InStr(1, OriginText, Chr(10))
If n = 0 Or Len(OriginText) = 0 Then Exit Sub
With Application
    .EnableEvents = False
    oldText = LCase(Target.Value)
    For i = 1 To Len(oldText)
        st = Mid(oldText, i, 1)
        If st = " " Then
            newText = newText & " "
        Else
            pos = InStr(1, OriginText, st)
            newText = newText & Mid(OriginText, pos + IIf(pos < n, n, -n), 1)
        End If
    Next
    Target.Value = Replace(newText, Chr(10), "")
    .EnableEvents = True
End With
End Sub

 

[Peter_SSs]

My question was not if it is the same problem as the other thread. My question is what version of Excel are you looking for an answer with? Your profile says 2007 but in that other thread you asked for and accepted an formula that only works in 365/2021.

BTW, please do not mark posts as the solution when they do not contain a solution. I have removed the solution mark from post #2 above.

 

[alliswell]

My question was not if it is the same problem as the other thread. My question is what version of Excel are you looking for an answer with? Your profile says 2007 but in that other thread you asked for and accepted an formula that only works in 365/2021.

BTW, please do not mark posts as the solution when they do not contain a solution. I have removed the solution mark from post #2 above.

Sorry peter. I didnt get you.
Ok my excel version is 2007. And i asked solution for 365/2021 as well coz i installed 365. But peter bro i prefer 2007 the most as my hands have adapted that version. If you can provide me in both versions will be better. That may educate me and make me understand the difference in those. And thanks peter. Provide me with formula if possible.

And thanks bebo for replying. But i need with formula if possible.

 

[bebo021999]

Sorry peter. I didnt get you.
Ok my excel version is 2007. And i asked solution for 365/2021 as well coz i installed 365. But peter bro i prefer 2007 the most as my hands have adapted that version. If you can provide me in both versions will be better. That may educate me and make me understand the difference in those. And thanks peter. Provide me with formula if possible.

And thanks bebo for replying. But i need with formula if possible.

I believe excel2016 or older are not strong with text combination job.
Only some new functions in 2019 + 365 help a lot.

 

[Peter_SSs]

But i need with formula if possible.

It is not feasible in 2007 with worksheet formulas so you would need to use 365/2021 or vba.

Does the text in A1 in your original image contain an Alt+Enter to split the text into 2 lines like that?

 

[alliswell]

It is not feasible in 2007 with worksheet formulas so you would need to use 365/2021 or vba.

Does the text in A1 in your original image contain an Alt+Enter to split the text into 2 lines like that?

Peter Yeah its alt+enter.
Ok provide me with 365/2021. Macro no.

Thanks bebo. If possible make formula. Any version

Dear peter n bebo, not necessary the way i asked to do so, you can also provide me by your method. I just need results the way i need. But thru formula.

Thanks again
Love ya all

 

[alliswell]

Peter Yeah its alt+enter.
Ok provide me with 365/2021. Macro no.

Thanks bebo. If possible make formula. Any version

Dear peter n bebo, not necessary the way i asked to do so, you can also provide me by your method. I just need results the way i need. But thru formula.

Thanks again
Love ya all

Dear peter still no reply ?