Convert multiple letters to ASCII and back to letters

[skittlz]

Hi Everyone,

I have a spreadsheet that manages revisions of different documents. I'm currently using this formula to convert letters to ASCII value:

=IFERROR(SUMPRODUCT(CODE(UPPER(MID(G2,ROW(INDIRECT("1:"&LEN(G2))),1)))),"")

I only want Capital letters, and this formula works. For A (or a), this formula gives me 65. For Z or z, i get 90. for AA, I get 130.

Problem: How do i convert 130 back to AA? My current formula is:

=IFERROR(CHAR(MAXIFS(Formulas!I:I,Formulas!A:A,'Training Matrix'!A7)),"")

For AA, I get a comma, for AB I get the "f" symbol, for AC i get 2 comas. Ideally, I need both formulas to work till ZZ.

Please help!

 

[skittlz]

@felixstraube - you're right - AA is bigger than Z, AZ is smaller than BA, and so on. Your formula makes sense - you're putting the ascii values of 2 letters together. I modified it so it coverts everything to upper case:

=LET(d, A1, l, LEN(d), exp, SEQUENCE(,l,l-1,-1), s, SEQUENCE(,l), SUM(CODE(UPPER((MID(d, s,1))))*100^exp) )

@rlv01 - i'm trying to work out how to make your formula work for 2 characters - it only seems to work for 4!

 

[Cubist]

If you want to pursue the ASCII idea, then the formulas can be simplified to this.

Book1
	A	B	C	D	E
1	Name	Document	Rev	Rev numerical	Reverse
2	Bill	Document11	E	69	E
3	Bill	Document11	F	70	F
4	Bob	Document10	AA	6565	AA
5	Bob	Document10	C	67	C
6	Bob	Document10	Z	90	Z
7	Bob	Document10	ZA	9065	ZA
8	Joe	Document12	G	71	G
9	Joe	Document12	H	72	H
Sheet5

Cell Formulas
Range	Formula
D2:D9	D2	=--CONCAT(CODE(MID(C2,SEQUENCE(LEN(C2)),1)))
E2:E9	E2	=CHAR(LEFT(D2,2))&IF(LEN(D2)=4,CHAR(RIGHT(D2,2)),"")

 

[Eric W]

If your Rev system follows the Excel column standard (A->Z,AA->AZ,BA->BZ, . . . , ZA->ZZ, AAA->AAZ, etc.), you can do something like this:

Book1
	A	B	C	D	E
1	Name	Document	Rev	Rev numerical	Reverse
2	Bill	Document11	E	5	E
3	Bill	Document11	F	6	F
4	Bob	Document10	AA	27	AA
5	Bob	Document10	C	3	C
6	Bob	Document10	Z	26	Z
7	Bob	Document10	ZA	677	ZA
8	Joe	Document12	G	7	G
9	Joe	Document12	H	8	H
Sheet9

Cell Formulas
Range	Formula
D2:D9	D2	=COLUMN(INDIRECT(C2&1))
E2:E9	E2	=SUBSTITUTE(ADDRESS(1,D2,4),1,"")

 

[rlv01]

@rlv01 - i'm trying to work out how to make your formula work for 2 characters - it only seems to work for 4!

That is odd. I don't have an issue.

Book1
	A	B	C	D	E
1	Name	Document	Rev	Rev numerical	Reverse
2	Bill	Document11	E	69	E
3	Bill	Document11	F	70	F
4	Bob	Document10	AA	6565	AA
5	Bob	Document10	C	67	C
6	Bob	Document10	Z	90	Z
7	Bob	Document10	ZA	9065	ZA
8	Joe	Document12	G	71	G
9	Joe	Document12	H	72	H
Sheet1

Cell Formulas
Range	Formula
D2:D9	D2	=IF(LEN(C2)>1,CODE(LEFT(C2,1)) & CODE(RIGHT(C2,1)),CODE(C2))
E2:E9	E2	=IF(LEN(D2)=4,CHAR(LEFT(D2,2)) & CHAR(RIGHT(D2,2)),CHAR(D2))

 

[felixstraube]

In case you have long rev strings (more than 3 characters):

Book1
	A	B	C	D	E
1	Name	Document	Rev	Rev numerical	Reverse
2	Bill	Document11	EFZG	69709071	EFZG
3	Bill	Document11	F	70	F
4	Bob	Document10	AA	6565	AA
5	Bob	Document10	C	67	C
6	Bob	Document10	AAAB	65656566	AAAB
7	Bob	Document10	AAAC	65656567	AAAC
8	Joe	Document12	ZAAB	90656566	ZAAB
9	Joe	Document12	AAXAAA	656588656565	AAXAAA
Sheet1

Cell Formulas
Range	Formula
D2:D9	D2	=LET(d, Sheet1!$C2, l, LEN(d), exp, SEQUENCE(,l,l-1,-1), s, SEQUENCE(,l), SUM(CODE(MID(d, s,1))*100^exp) )
E2:E9	E2	=LET(n, D2, l, LEN(n)/2, CONCAT(CHAR(MID(n, SEQUENCE(,l,1,2),2))) )