Excel Spreadsheet

[lindagrimm8251]

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.

[#2]

Do you any list of letter and value against it? If you have, then it would be possible.

[#3]

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.

[#4 · Accepted solution]

@lindagrimm8251 

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]

Thank so much for your help! I have my spreadsheet all set up and working great.

[#6]

You're welcome!