If you have a list or table of either ALPHABETS or TEXTS, it is possible to SUM the TEXT values once we have ASSIGNED NUMBERS to the TEXT or ALPHABET as the case may apply.
The advantage of this in excel is obvious. You can use it to evaluate the content of a digital questionnaire amongst others.
HOW TO SUM TEXT VALUES AS NUMBERS
To CONVERT TEXT values into their ASSIGNED NUMBERS, and then, SUM the result in excel, we can use the INDEX, MATCH, and SUM functions. As shown in the example and the curated steps below, the basic formula in Cell H5 is:
How To Sum Or Add Text Or Alphabetical Values As Numbers
Input your data as shown in the figure below. Assign a particular number for the ALPHABET or TEXT as the case may apply. We have four combined alphabets in two’s in figure 1 below; AG, UN, PN, and GN.
Figure 1- How To Sum Or Add Text Or Alphabetical Values Like Numbers
Click on Cell H5 in figure 2. Then, click on the fx bar to input the following below. You can also copy and paste it. This will work just fine.
Figure 2- How To Sum Or Add Text Or Alphabetical Values Like Numbers
Hold the CTRL+SHIFT BUTTONS and PRESS the ENTER BUTTON. After doing this, you should have the figure below which displays the answer we want, 25.
Figure 3- How To Sum Or Add Text Or Alphabetical Values Like Numbers
Now, let’s explain what we have done. The generic formula for this process is given below:
The functions that enable the translation of the ALPHABETS or TEXTS to NUMBERS are the INDEX and MATCH functions. The N and IF functions are required to ensure that we get results for the entire row. A shorter stream of functions will result in producing values for just a set of alphabets or text.
In this example, in the place of VALUE, you will notice that the digits begin from Cell J5 and ends at J8. Hence, your value should look like this, J5:J8.
The CODE as shown on the figures begin from Cell I5 and ends at Cell I8. Hence, your code should look like this I5:I8.
Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.