1

I would like to know how to assign a specific value to selected letters and later on use the letters in a formula which will return a value.

There are few letters in one cell, they can be seperated by spaces, commas or dots, doesn't really matter but they need to be in the same cell.

Screenshot for a better explanation:

enter image description here

1 Answer 1

1

Use REGEXREPLACE or SUBSTITUTE to replace the letters by numbers, then SPLIT to separate the values into an array of values and put that inside the formula that will use that values to do something.

If you didn't do this yet, look for the description of each function on https://support.google.com/docs or the list of functions on the Help menu from Google Sheets.


Let say that we put the letters in A1:A3, then the corresponding values in B1:B3.

A B
1 A 5
2 B 10
3 C 15

With a separator (in this case coma)

The value A,B,C to be converted is in C1. Using SPLIT

=ArrayFormula(SUM(IFERROR(VALUE(SUBSTITUTE(SPLIT(C1,","),A1:A3,B1:B3)),0)))

Using regular expressions

=ArrayFormula(SUM(IFERROR(VALUE(SUBSTITUTE(REGEXEXTRACT(C1,"(.),(.),(.)"),A1:A3,B1:B3)),0)))

Without a separator The value ABC to be converted is in C1. In this case we can't use split as it requires a seperator

=ArrayFormula(SUM(IFERROR(VALUE(SUBSTITUTE(REGEXEXTRACT(C1,"(.)(.)(.)"),A1:A3,B1:B3)),0)))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.