Tags: REPT

Convert a text entry into its number equivalent

{0 Comments}

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.

Summarise data from multiple sheets with one condition – PartII

{2 Comments}

Assume a worksheet which has scores (1-10) on four future dates on nine questions.  Each row represents responses for one question.  Likewise there are 20 respondents i.e. 20 worksheets which have the same structure.

One may want to summarise data from all 20 worksheets into one worksheet.  The question and solution are more clearly explained in the attached file.

I have presented two cases here - Example 1 and Example 2 in this workbook.

You may also want to refer to the following related posts:

1. Summarise data from multiple sheets with one condition
2. Summarise data from multiple sheets with multiple conditions
3. Generate a list of all tab names without using VBA