SOLVED

Copper Contributor

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.

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.
best response confirmed by lindagrimm8251 (Copper Contributor)
Solution

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.

You're welcome!
1 best response

Accepted Solutions
best response confirmed by lindagrimm8251 (Copper Contributor)
Solution

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.