Substitution of letters

[elle_clara]

Hi, I've been trying to make a macro for making a simple substitution cypher but although I managed to do so in Python I don't know enough to make a macro.

The desired outcome would be to type a phrase in, say, cell A1, run the macro and then have the result appear in cell A2.

It's not a simple offset by a given number of characters (like a->c, b->d, etc) but rather a substitution that I'd write for each and every letter on a different row, e.g:
a->s
b->a
c->t

A1: abc -> A2: sat

I guess I'd need to use the Replace function but I'm not sure how to make it write in cell A2 the substituted form.

 

[elle_clara]

For example..

Dim x As String
x = ChrW(&H15F)
sFm = "ABC & X"

I mean, something like this; where you could enter your Unicode characters directly in the cells in "To" column on the worksheet.

Excel 2013

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

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]

[TD="align: center"]18[/TD]

[TD="align: center"]19[/TD]

[TD="align: center"]20[/TD]

[TD="align: center"]21[/TD]

[TD="align: center"]22[/TD]

[TD="align: center"]23[/TD]

[TD="align: center"]24[/TD]

[TD="align: center"]25[/TD]

[TD="align: center"]26[/TD]

[TD="align: center"]27[/TD]

</tbody>

Sheet1​

I tried using

Dim x As String
x = ChrW(&H15F)

and replacing in the sFm and sTo but it doesn't work.

That replacement table would be perfect! How should I write the instructions? I suppose it will be 28 rows (not that I mind) of the type I1 → J1. That "→" there is my problem :P no idea how to do it. It would be awesome to keep it as a function with text entered in A1 and appearing in the coded form in A2.

Also thanks for bearing with me :D

 

[FormR]

Hi, try like this:

Function cypher(s As String, rFrom As Range, rTo As Range, Optional Decode As Boolean) As String
Dim sFm As String, sTo As String
sFm = Join(Application.Transpose(rFrom), "")
sTo = Join(Application.Transpose(rTo), "")
Dim i As Long, n As Long
For i = 1 To Len(s)
    n = InStr(1, IIf(Decode, sTo, sFm), Mid(s, i, 1), vbTextCompare)
    If n Then
        cypher = cypher & Mid(IIf(Decode, sFm, sTo), n, 1)
            Else
        cypher = cypher & Mid(s, i, 1)
    End If
Next i
End Function

Note: cell F6 contains the "→" character; but the MrExcelHTML add-in can't display Unicode characters so it will appear as a "?"

Excel 2013
	A	B	C	D	E	F
1	hello world	s?ool dliow	hello world		From	To
2	mr excel	ni ?cx?o	mr excel		a	z
3	formr	ulini	formr		b	y
4	elle_cara	?oo?_xziz	elle_cara		c	x
5					d	w
6					e	?
7					f	u
8					g	t
9					h	s
10					i	r
11					j	q
12					k	p
13					l	o
14					m	n
15					n	m
16					o	l
17					p	k
18					q	j
19					r	i
20					s	h
21					t	g
22					u	f
23					v	e
24					w	d
25					x	c
26					y	b
27					z	a
Sheet1

Cell Formulas
Range	Formula
B1	=cypher(A1,$E$2:$E$27,$F$2:$F$27)
C1	=cypher(B1,$E$2:$E$27,$F$2:$F$27,TRUE)
F6	=UNICHAR(8594)

 

[elle_clara]

It's awesome! It works so well, way better than the Python thing I did.
Thanks so much!