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.

 

[wesborland1234]

Something like this:

Cells(1, 2).Value = replace(Cells(1, 2).Value, "old", "new")

 

[FormR]

Hi, here is a UDF that you could try:

Function cypher(s As String, Optional Decode As Boolean) As String
Const sFm = "abcdefghijklmnopqrstuvwxyz"
Const sTo = "zxcvbnmasdfghjklqwertyuiop"
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

Which could be used in the worksheet like this, for example:

Excel 2013
	A	B	C
1	hello world	abggk ukwgv	hello world
2	mr excel	hw bicbg	mr excel
3	FormR	nkwhw	formr
4	elle_clara	bggb_cgzwz	elle_clara
Sheet1

Cell Formulas
Range	Formula
B1	=cypher(A1)
C1	=cypher(B1,TRUE)

Or called via code like this for example:

Sub EncodeIt()
Range("B1").Value = cypher(Range("A1").Value)
End Sub


Sub DecodeIt()
Range("C1").Value = cypher(Range("B1").Value, True)
End Sub

 

[elle_clara]

Something like this:

Cells(1, 2).Value = replace(Cells(1, 2).Value, "old", "new")

Hey thanks, I don't really know how cells(x,y) works and I get an error. I tried with Range("A2").Value=replace(Range("A1").Value, "a", "s") and it works, but only sort of. It works only for the most recent row. So in my example it just replaces "c"→"t".

Hi, here is a UDF that you could try:

It's awesome! It works really well, I love the fact that I can use it as a function just like any other in excel.
One last question: I know that if I want to use a non-ASCII character I should use ChrW(&H1234) where 1234 is the unicode hex code. But I'm not sure how to insert it in the letter strings sFm and sTo.
I don't mind writing the unicode hex codes for each and every character if there's a way to make it work with the current way it makes the replacements. But if a whole new approach is needed don't worry, it's really good as it is even now!

 

[FormR]

Hi, not tested as just about to pop out for a while, but you could try something like this:

Function cypher(s As String, Optional Decode As Boolean) As String
Dim sFm As String
Dim sTo As String


sFm = "abcdefghijklmnopqrstuvwxyz" & ChrW(&H1234) & ChrW(&H4567)
sTo = "zxcvbnmasdfghjklqwertyuiop" & ChrW(&H4567) & ChrW(&H1234)


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

 

[jwalrath]

Is there a way to get this to work if it is a combination of numbers and letters in the cell?

 

[elle_clara]

Hi, not tested as just about to pop out for a while, but you could try something like this:

Ugh it gives the error "constant expression required". I tried to define a constant x as
Const x = ChrW(&H15F)
and use it in the sFm as [...] & x, but the same error appears.
I probably don't understand how constants work in Excel. But don't worry it's really good this way already!

@jwalrath yes, it works if you want to replace a letter by a digit.

 

[FormR]

Hi, you would need to define x as a string and then assign it's value.

It sounds like it might be easier for you to define the "To" and "From" characters in a range on the worksheet - it wouldn't be a big deal to change the UDF if that method would be preferable?

 

[elle_clara]

Hi, you would need to define x as a string and then assign it's value.

It sounds like it might be easier for you to define the "To" and "From" characters in a range on the worksheet - it wouldn't be a big deal to change the UDF if that method would be preferable?

How could I define it as a string?

Const x = ChrW(&H15F) as String

I'm sorry, I'm really a noobie in VBA.

As for characters defined in a range, do you mean like bellow?

A1: abcde [...]
A2: hskrz [...]

If so, yes that would be really good! Can you tell me how should I alter the function, pretty please?

 

[FormR]

How could I define it as a string?

For example..

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

As for characters defined in a range, do you mean like bellow?

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
1	From	To
2	a	z
3	b	x
4	c	c
5	d	v
6	e	b
7	f	n
8	g	m
9	h	a
10	i	s
11	j	d
12	k	f
13	l	g
14	m	h
15	n	j
16	o	k
17	p	l
18	q	q
19	r	w
20	s	e
21	t	r
22	u	t
23	v	y
24	w	u
25	x	i
26	y	o
27	z	p
Sheet1