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.

 

[Josh324]

space=21
A=13
B=15
C=16
D=17
E=18
F=19
G=20
H=22
I=23
J=24
K=25
L=26
M=27
N=28
O=31
P=33
Q=34
R=35
S=36
T=37
U=38
V=40
W=41
X=42
Y=44
Z=45
.=46
‘=47
/=48
-=49
+=50
&=51
(=52
)=53

 

[GWteB]

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

 

[Taul]

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),"-")

 

[Peter_SSs]

For example if I was to type hello need a number 22 23 26 26 35.

That doesn't seem to match with the values from post #2 but see if this would work for you with standard worksheet functions and a lookup table.
F1 needs to actually contain a space character and F29 would need to have two single quote marks entered to get one to show (as Taul has already mentioned)

Josh324.xlsm
	A	B	C	D	E	F	G
1	hello	2218262631					21
2	goodbye	20313117154418				A	13
3	John & Mary-Jo O'Connor/(Hill)	243122282151212713354449243121314716312828313548522223262653				B	15
4						C	16
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
30						/	48
31						-	49
32						+	50
33						&	51
34						(	52
35						)	53
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=CONCAT(VLOOKUP(MID(A1,SEQUENCE(LEN(A1)),1),F$1:G$35,2,0))

 

[Rick Rothstein]

Assuming your number values per letter will never change, here is a UDF (user defined function) solution that does not require you to maintain a translation table on a worksheet... the function is wholly self-contained. Simply pass the text (as a quoted text string or a reference to a cell containing the text you want to translate) into the function and out will come your long number.

Function LetterValues(s As String) As String
  Dim X As Long
  Const Letters As String = "A~BCDEFG HIJKLMN~~O~PQRSTU~VWX~YZ.'/-+&()"
  For X = 1 To Len(s)
    LetterValues = LetterValues & 12 + InStr(1, Letters, Mid(s, X, 1), vbTextCompare)
  Next
End Function

 

[Peter_SSs]

solution that does not require you to maintain a translation table on a worksheet

No, but IMHO is less intuitive & requires vba to be enabled.
Such a string approach could also be implemented in a self-contained worksheet formula without the vba if there was a desire to avoid a lookup table.

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

Cell Formulas
Range	Formula
B1:B3	B1	=CONCAT(12+SEARCH(MID(A1,SEQUENCE(LEN(A1)),1),"A~BCDEFG HIJKLMN~~O~PQRSTU~VWX~YZ.'/-+&()"))

 

[Rick Rothstein]

Such a string approach could also be implemented in a self-contained worksheet formula...

Providing, of course, that your version of Excel has the CONCAT function in it. And yes, I know the OP's version has that function but it would have been hard for me to develop such a formula in my version of Excel, so I fell back on using a UDF.

 

[Josh324]

No, but IMHO is less intuitive & requires vba to be enabled.
Such a string approach could also be implemented in a self-contained worksheet formula without the vba if there was a desire to avoid a lookup table.

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

Cell Formulas
Range	Formula
B1:B3	B1	=CONCAT(12+SEARCH(MID(A1,SEQUENCE(LEN(A1)),1),"A~BCDEFG HIJKLMN~~O~PQRSTU~VWX~YZ.'/-+&()"))

Brilliant! Thank you so much for your brilliant formula.

 

[Josh324]

Assuming your number values per letter will never change, here is a UDF (user defined function) solution that does not require you to maintain a translation table on a worksheet... the function is wholly self-contained. Simply pass the text (as a quoted text string or a reference to a cell containing the text you want to translate) into the function and out will come your long number.

Function LetterValues(s As String) As String
  Dim X As Long
  Const Letters As String = "A~BCDEFG HIJKLMN~~O~PQRSTU~VWX~YZ.'/-+&()"
  For X = 1 To Len(s)
    LetterValues = LetterValues & 12 + InStr(1, Letters, Mid(s, X, 1), vbTextCompare)
  Next
End Function

Thank you for your help but unfortunately this seems to complicated as I am not a wizard on excel ?.