Consider this simple two column table showing text entries in column A and the corresponding numbers in column. Assume this data is in range A2:B11 (headings are in A1:B1).
| text | Value |
| A | 1 |
| B | 2 |
| C | 3 |
| D | 4 |
| E | 5 |
| F | 6 |
| G | 7 |
| H | 8 |
| I | 9 |
| J | 0 |
The objective is to generate the numeric code for text code of any length entered in a certain cell. For example, a user will type a certain text code, say ABEJ and the expected result should be 1250. For JABF, the result should be 0126. The text entry and text length are both user determined.
With ABEJ, typed in cell D2, enter this array formula in cell E2
=TEXT(SUMPRODUCT((LOOKUP(MID(D2,ROW(INDIRECT(“1:”&LEN(D2))),1),$A$2:$A$11,$B$2:$B$11))*((10^(LEN(D2)-1-(ROW(INDIRECT(“1:”&LEN(D2)))-1))))),REPT(“0”,LEN(D2)))
This formula can now be copied down for generating the numeric code for all text codes entered in column D.
Convert a text entry into its number equivalent
{ 0 Comments }