Formula For Gematria

[Balcomie]

Row A1 to Z26 has the letters A to Z and Row A2 to Z2 has the numbers 1 to 26 and each column is reduced to 3 characters, so I am looking for a formula that I can incorporate underneath "say" in Row 5 where using each box to place a letter of the sentence - The sun shines in Florida - directly under each letter the formula identifies the number value of each letter from area A1 to Z2 in other words as an example using the first word
"The" T = 20 - H = 8 - E = 5 and so on - I am not sure how I would deal with longer sentences which would use "say" rows 5 and 7 but I would appreciate some help on this

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
1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25
T	h	e	S	u	n	S	h	i	n	e	s	i	n	F	l	o	r	i	d	a
20	8	5	19	21	14	19	8	9	14	5	19	9	14	6	12	15	18	9	4	1

 

[StephenCrump]

Perhaps something along these lines?

	A	B	C	D	E	F	G	H
1	The Sun Shines		T	h	e	S	u	n
2	TheSunShines		20	8	5	19	21	14
Sheet1

Cell Formulas
Range	Formula
C1:H1	C1	=MID($A2,COLUMNS($C1:C1),1)
C2:H2	C2	=CODE(UPPER(C1))-64
A2	A2	=SUBSTITUTE(A1," ","")

 

[Balcomie]

Absolutely brilliant Thank You

 

[StephenCrump]

You're welcome.

Perhaps you could let us know in your Account Details which version of Excel you're using.

If you have 365, you can use a single formula

=LET(a,SUBSTITUTE(A1," ",""),b,MID(a,SEQUENCE(,LEN(a)),1),VSTACK(b,CODE(UPPER(b))-64))