+ Reply to Thread
Results 1 to 12 of 12

Assigning a numerical value to each letter in a string, and adding them up

  1. #1
    Registered User
    Join Date
    07-24-2013
    Location
    Bratislava, SK
    MS-Off Ver
    Office Pro Plus 2016
    Posts
    42

    Assigning a numerical value to each letter in a string, and adding them up

    Hello again,

    I have assigned a numeric value to each letter of the alphabet, A=1, B=2, C=3, D=4, etc.

    I have a string of characters in cell A1, let's say "ABCD". In cell B1, I'd need a function that can substitute each of the individual characters in the string (not case sensitive, i.e. a=1 and A=1) into their respective numbers, and then add each of the numbers.

    So in this example, my string in A1 being ABCD, the result in B1 would be 10 (i.e. 1+2+3+4). If my string in A1 was AAAA, the result in B1 would be 4 (1+1+1+1).

    Is that in any way possible? I've searched google for hours, and I've racked my brain for a solution, but I can't seem to find one.

    Thanks for your insight

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Assigning a numerical value to each letter in a string, and adding them up

    Hi,

    Assuming you have a table in Sheet1 containing letters of the alphabet in A1:A26 with their respective scores in B1:B26 then, in B1 of your results sheet:

    =SUMPRODUCT(SUMIF(Sheet1!$A$1:$A$26,MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1),Sheet1!$B$1:$B$26))

    Copy down to give similar scores for strings in A2, A3, etc.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Assigning a numerical value to each letter in a string, and adding them up

    Assuming Alphabet Values are in D1:E4 and A= ABCD

    Then

    B1
    Formula: copy to clipboard
    Please Login or Register  to view this content.




    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Check The attached file.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Assigning a numerical value to each letter in a string, and adding them up

    Something like this
    Enter in E1 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B C D E
    1 A 1 ABC 6
    2 B 2 ABD 7
    3 C 3 C 3
    4 D 4 ABCD 10
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: Assigning a numerical value to each letter in a string, and adding them up

    For VBA Code: assuming Column A contains Alphabet and B contains Numbers:

    *updated file for button to run code*
    Attached Files Attached Files
    Last edited by PFDave; 04-28-2017 at 08:45 AM.

  6. #6
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Assigning a numerical value to each letter in a string, and adding them up

    @AlKey

    Letters may be repeated:

    Quote Originally Posted by isabelle.r View Post
    If my string in A1 was AAAA, the result in B1 would be 4 (1+1+1+1).

  7. #7
    Registered User
    Join Date
    07-24-2013
    Location
    Bratislava, SK
    MS-Off Ver
    Office Pro Plus 2016
    Posts
    42

    Re: Assigning a numerical value to each letter in a string, and adding them up

    XOR LX: Nicely implemented solution. The function works perfectly, but it must be somewhat resource intensive, as it takes a while to react. It also takes a few seconds more to open the workbook.

    PFDave: I know I asked for a function, but I can't help marvel at the elegance and simplicity of the VBA code. It's also lightning-quick.

    I'll look at both further in detail, but I love these solutions.

    Thank you all for participating in this perhaps silly quest of mine

  8. #8
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Assigning a numerical value to each letter in a string, and adding them up

    Quote Originally Posted by isabelle.r View Post
    XOR LX: Nicely implemented solution. The function works perfectly, but it must be somewhat resource intensive, as it takes a while to react. It also takes a few seconds more to open the workbook.
    It will only be resource-intensive if you pass a very large range to the function. But then you are only considering 26 rows, correct, one for each letter of the alphabet? I trust you didn't hope to 'cover your bases' by in actuality referencing a much larger range (and certainly not entire columns!)?

    Regards

  9. #9
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: Assigning a numerical value to each letter in a string, and adding them up

    Quote Originally Posted by isabelle.r View Post
    PFDave: I know I asked for a function, but I can't help marvel at the elegance and simplicity of the VBA code. It's also lightning-quick.

    I'll look at both further in detail, but I love these solutions.

    Thank you all for participating in this perhaps silly quest of mine
    You're welcome. I thought I might as well chuck my view in as I suspect this may be part of a bigger picture for which VBA should be leaner.

    Thanks for the rep

  10. #10
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Assigning a numerical value to each letter in a string, and adding them up

    Here is an improved formula
    Enter in E1 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B C D E
    1 A 1 ABC 6
    2 B 2 DEF 15
    3 C 3 AF 7
    4 D 4 CD 7
    5 E 5 EAF 12
    6 F 6 EEFF 22
    7 G 7 AAAA 4
    8 H 8 AAAAAAA 7
    9 I 9 EEBBCCC 23
    10 J 10 QWERTY 108
    11 K 11
    12 L 12
    13 M 13
    14 N 14
    15 O 15
    16 P 16
    17 Q 17
    18 R 18
    19 S 19
    20 T 20
    21 U 21
    22 V 22
    23 W 23
    24 X 24
    25 Y 25
    26 Z 26

  11. #11
    Registered User
    Join Date
    07-24-2013
    Location
    Bratislava, SK
    MS-Off Ver
    Office Pro Plus 2016
    Posts
    42
    Quote Originally Posted by PFDave View Post
    You're welcome. I thought I might as well chuck my view in as I suspect this may be part of a bigger picture for which VBA should be leaner.

    Thanks for the rep
    One little request, if you have a few minutes time. Would you mind commenting your code a little, to help those of us who aren't (yet) at your level to better see what's going on?

  12. #12
    Registered User
    Join Date
    04-28-2015
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365
    Posts
    60

    Re: Assigning a numerical value to each letter in a string, and adding them up

    Can this Be modified to work for Alphanumeric Strings i.e Cat1 Dog22 and returning numerical values

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA for assigning numerical values to each letter of the alphabet and SUMming strings
    By arthurspooner in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-20-2016, 09:54 PM
  2. [SOLVED] Adding together letter grades and assigning an overall grade
    By rufusbabe in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-23-2014, 07:52 PM
  3. [SOLVED] Assigning numerical value to text entries
    By SecureData in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-21-2013, 10:09 AM
  4. Replies: 5
    Last Post: 08-21-2013, 09:31 AM
  5. Assigning a numerical value to a letter in excel
    By lisa07956 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-15-2013, 09:45 AM
  6. Adding spaces to every third letter in a string
    By Einstahb in forum Excel General
    Replies: 3
    Last Post: 09-01-2011, 03:53 PM
  7. Replies: 7
    Last Post: 06-16-2008, 05:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1