Converting numbers to special symbols and converting the symbols back to numbers using vba

[kelly mort]

I am trying to assign symbols to digits as below:

0 = !
1 = @
2 = #
3 = <
4 = ~
5 = :
6 = $
7 = %
8 = ?
9 = &

So that When I have a number like "2031", I want to convert it to:

#! <@

And in the case of ?~$$ I want to convert it to 8466

How do I do that?

Thanks in advance.
Kelly

 

[Rick Rothstein]

Hi Rick,​

​

why not an unique function rather than two ? …​

I guess I'm kind of old school... each function does one thing which in turn makes the code a little more self documenting. For a single function using my approaches...

Function Crypto(S As String) As String
  Dim X As Long
  Crypto = S
  For X = 1 To Len(S)
    If Left(S, 1) Like "#" Then
      Mid(Crypto, X) = Mid("!@#<~:$%?&", Mid(S, X, 1) + 1, 1)
    Else
      Mid(Crypto, X) = InStr("!@#<~:$%?&", Mid(S, X, 1)) - 1
    End If
  Next
End Function

 

[Rick Rothstein]

Okay, as it turns out, the functions I post are also too long. Here are revised functions that, for my individual functions, are one-liners and while not a one-liner, the "unique" function Marc asked about is still quite compact. Here are these functions...

Function Num2Sym(S As String) As String
  Num2Sym = Join(Evaluate("TRANSPOSE(MID(""!@#<~:$%?&"",MID(""" & S & """,ROW(1:" & Len(S) & "),1)+1,1))"), "")
End Function

Function Sym2Num(S As String) As String
  Sym2Num = Join(Evaluate("TRANSPOSE(FIND(MID(""" & S & """,ROW(1:" & Len(S) & "),1),""!@#<~:$%?&""))-1"), "")
End Function

Function Crypto(S As String) As String
  If S Like "#*" Then
    Crypto = Join(Evaluate("TRANSPOSE(MID(""!@#<~:$%?&"",MID(""" & S & """,ROW(1:" & Len(S) & "),1)+1,1))"), "")
  Else
    Crypto = Join(Evaluate("TRANSPOSE(FIND(MID(""" & S & """,ROW(1:" & Len(S) & "),1),""!@#<~:$%?&""))-1"), "")
  End If
End Function