Forum Discussion
Solved
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 l...
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.
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.
- Thank so much for your help! I have my spreadsheet all set up and working great.
