Please help

[Josh324]

Is there a way I can convert the letter A to 13 and B to 15 number won’t be in order with the letter. need to be able to type a word and for it to spit out a very long number.
For example if I was to type hello need a number 22 23 26 26 35.
its for an old alarm panel I need to program and it only allows number input so wanted to know if there was a way I could decode instead of pen and paper. Any help would be much appreciated.

 

[Peter_SSs]

Brilliant! Thank you so much for your brilliant formula.

You're welcome - but we better give Rick credit for manufacturing the string.

If you did want an alternative that is a bit easier to follow (but somewhat longer) you could consider the version below. In this formula, each pair of digits in the number string correspond in order to the characters in the text string. I have matched a few with colours to illustrate (hard to colour the space though )

=CONCAT(MID("2113151617181920222324252627283133343536373840414244454647484950515253",SEARCH(MID(A1,SEQUENCE(LEN(A1)),1)," ABCDEFGHIJKLMNOPQRSTUVWXYZ.'/-+&()")*2-1,2))

Josh324.xlsm
	A	B
1	hello	2218262631
2	goodbye	20313117154418
3	John & Mary-Jo O'Connor/(Hill)	243122282151212713354449243121314716312828313548522223262653
Sheet3

Cell Formulas
Range	Formula
B1:B3	B1	=CONCAT(MID("2113151617181920222324252627283133343536373840414244454647484950515253",SEARCH(MID(A1,SEQUENCE(LEN(A1)),1)," ABCDEFGHIJKLMNOPQRSTUVWXYZ.'/-+&()")*2-1,2))

 

[Josh324]

If it is no problem for you to use a worksheet for this, you could set up this worksheet as below.

Book1
	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	AA	AB	AC	AD	AE	AF	AG	AH	AI
1	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		.	'	/	-	+	&	(	)
2	13	15	16	17	18	19	20	22	23	24	25	26	27	28	31	33	34	35	36	37	38	40	41	42	44	45	21	46	47	48	49	50	51	52	53
3	(hello)	52221826263153
4	o /'+&-	31214847505149
Sheet1

Cell Formulas
Range	Formula
B3:B4	B3	=DoCode(A3)

A custom function is used (DoCode). To make this work copy the code and open the VBA editor by pressing ALT F11. Within VBE press ALT i followed by ALT m to insert a standard module. Paste the code of the custom function in the just opened pane.

Public Function DoCode(ByRef argString As String) As String

    Dim i As Long, s As String, r As Range, c As Range

    Application.Volatile
    With ThisWorkbook.Sheets("Sheet1")
        Set r = .Range(.Cells(1, 1), .Cells(1, .Columns.Count).End(xlToLeft))
    End With
    For i = 1 To Len(argString)
        Set c = r.Find(Mid(argString, i, 1), , xlValues, xlWhole)
        s = s & c.Offset(1).Value
    Next i
    DoCode = s
End Function

Thank you for your rapid response will try this one tonight

 

[Josh324]

Hi,
Another way using Vlookup
You will need to type '/ to get / and '' to get '

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		21	actual space in the cell A1
2	A	13
3	B	15				m	y	/	p	a	s	s	y	&		)	.	'
4	C	16				27	44	48	33	13	36	36	44	51	21	53	46	47	-	-
5	D	17
6	E	18
7	F	19
8	G	20
9	H	22
10	I	23
11	J	24
12	K	25
13	L	26
14	M	27
15	N	28
16	O	31
17	P	33
18	Q	34
19	R	35
20	S	36
21	T	37
22	U	38
23	V	40
24	W	41
25	X	42
26	Y	44
27	Z	45
28	.	46
29	'	47	need to type as two ''
30	/	48	need to type as '/
31	-	49
32	+	50
33	&	51
34	(	52
35	)	53
Sheet1

Cell Formulas
Range	Formula
F4:T4	F4	=IFERROR(VLOOKUP(F$3,$A$1:$B$35,2,FALSE),"-")

Thanks for your help will try this tonight on my laptop.