Forum Discussion
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 pricing code is PATHFINDER (whereas P=1, A=2, T=3, H=4, F=5, I=6, N=7, D=8, E=9, and R=0)
So, $12.45 would be PAHF, $5.60 would be FIR, etc.
Here is the formula I used to convert a letter code to prices.
=LET(l, LEN(C3), letters, MID(C3, SEQUENCE(l), 1), numbers, CONCAT(SWITCH(letters, "P", 1, "A", 2, "T", 3, "H", 4, "F", 5, "I", 6, "N", 7, "D", 8, "E", 9, "R", 0)), numbers / 100)
In this formula, C3 had the text "PAHF" and the formula produced $12.45.
Please help me find a way to take a price and convert it to the letter code instead (the opposite of what I did, essentially).
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"}))
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"}))Thank you so much! This worked perfectly! I went with the first one and my spreadsheet is perfect. You've been a huge help!
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 )
