Forum Discussion
Excel Spreadsheet
I am only a basic Excel user and I am trying to create a spreadsheet to use for an inventory count.
I have a category that is the 4 digit code for the price paid for an item shown in letters (each letter is a different number) i.e. FRED would be $12.45. I would like to convert the code to the actual price paid on the spreadsheet. I don't know if the is possible or not and have not had much luck trying to figure out if it can be done.
Thank you for the details. Here is what the sheet level formula looks like:
=LET( l, LEN(C3), letters, MID(C3, SEQUENCE(l), 1), numbers, CONCAT( SWITCH(letters, "F", 1, "R", 2, "I", 3, "E", 4, "D", 5, "A", 6, "J", 7, "V", 9, "N", 0, 0) ), numbers / 100 )If you need to do this often, it makes sense to create a dedicated function.
//Convert letter string to a price. Not all letters have //corresponding numbers. //0 is returned if letter is not F, R, I, E, D, A, J, V, or N. GetPrice = LAMBDA(letters, LET( l, LEN(letters), str, MID(letters, SEQUENCE(l), 1), numbers, CONCAT( SWITCH(str, "F", 1, "R", 2, "I", 3, "E", 4, "D", 5, "A", 6, "J", 7, "V", 9, "N", 0, 0) ), numbers / 100 ) )This is what the above GetPrice function looks like in the sheet.
I've attached a workbook that contains the formula longhand and the folded up GetPrice Lambda.
5 Replies
- Do you any list of letter and value against it? If you have, then it would be possible.
- I have a value for all the letters I am using
F =1
R =2
I =3
E = 4
D = 5
A = 6
J = 7
V = 9
N = 0
I just don't know how to put it all together in a spreadsheet.Thank you for the details. Here is what the sheet level formula looks like:
=LET( l, LEN(C3), letters, MID(C3, SEQUENCE(l), 1), numbers, CONCAT( SWITCH(letters, "F", 1, "R", 2, "I", 3, "E", 4, "D", 5, "A", 6, "J", 7, "V", 9, "N", 0, 0) ), numbers / 100 )If you need to do this often, it makes sense to create a dedicated function.
//Convert letter string to a price. Not all letters have //corresponding numbers. //0 is returned if letter is not F, R, I, E, D, A, J, V, or N. GetPrice = LAMBDA(letters, LET( l, LEN(letters), str, MID(letters, SEQUENCE(l), 1), numbers, CONCAT( SWITCH(str, "F", 1, "R", 2, "I", 3, "E", 4, "D", 5, "A", 6, "J", 7, "V", 9, "N", 0, 0) ), numbers / 100 ) )This is what the above GetPrice function looks like in the sheet.
I've attached a workbook that contains the formula longhand and the folded up GetPrice Lambda.
