Here is my situation.
In column A, each cell contains random strings of different letters, numbers and spaces. They are all the same length (200 characters)
Here is what I am looking to do. I want to create my own algorithm (A= 1.05, B= 1.12, etc, all the way up to Z and then also I want to assign values to numbers 0-9, and a value to the space character)
I want to know, based on my algorithm, what the total value is of all 200 characters in cell A2. I am aiming for a specific total value, so if I could take it a step further I would like to have a formula to remove a few characters from the end of the text string if the total value is above what I am aiming for.
The second part is not 100% necessary if its too difficult, I can just do it manually if I have to. But I would definitely like to at least accomplish the first part of what I am trying to do if possible.
If you think I might be better off with a macro/VBA, let me know and I will ask this over there. But I think I can at least conquer part 1 of this equation with regular formulas, maybe a VLOOKUP or something?
.....
If I seem unclear as to what I am asking, here is an example:
A2: Y77EPZ G0BGQV STZNW9 D2VLC FN5DH RZGLEPPPCDZE
A3: 18U1X03S3X57 ZDUFIPU KRTGKEB E9K N7F VZOZS4SH
A4: S135UPKB5EP3 W5YVT U4 U8 DOWYM6 YP562ZOE8Q51Q
IF the letter "A" = 1.1, "B" = 1.25, "C" = 3.2 , "D" = 0.88, etc etc, then what is the sum of all characters in A2? What about the sum of the characters in A3? A4? (Not trying to add the whole column together, just an individual sum for each cell, displayed in the adjacent cell)
Bookmarks