Forum Discussion
Solved
Need help reversing a formula
I'm trying to create a formula that will take a price from one cell and convert it to a letter code. I found a formula that does the opposite, it takes a letter code and converts it to a price. The...
You may try the following formula-
=CONCAT(XLOOKUP(--MID(C3*100,SEQUENCE(LEN(C3*100)),1), {1,2,3,4,5,6,7,8,9,0},{"P","A","T","H","F","I","N","D","E","R"}))Dynamic spill formula
=MAP(C3:C12,LAMBDA(x, CONCAT(XLOOKUP(--MID(x*100,SEQUENCE(LEN(x*100)),1), {1,2,3,4,5,6,7,8,9,0},{"P","A","T","H","F","I","N","D","E","R"}))))Another option-
=CONCAT(XLOOKUP(LET(x,SUBSTITUTE(TEXT(C3,"0.00"),".",""),--MID(x,SEQUENCE(LEN(x)),1)), {1,2,3,4,5,6,7,8,9,0},{"P","A","T","H","F","I","N","D","E","R"}))
Try this:
=LET(
price, TEXT(A1*100, "0000"),
digits, MID(price, SEQUENCE(LEN(price)), 1),
letters, TEXTJOIN("", TRUE, SWITCH(digits, 1, "P", 2, "A", 3, "T", 4, "H", 5, "F", 6, "I", 7, "N", 8, "D", 9, "E", 0, "R")),
letters
)
