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.
=SUM(ABS(A1:A4))
5 Replies
- Harun24HRBronze ContributorDo you any list of letter and value against it? If you have, then it would be possible.
- lindagrimm8251Copper ContributorI 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.- Patrick2788Silver Contributor
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.