Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
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:
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)))
