Excel - Formula or code to determine the numerical value of a character string

Experts,
This is really a two part question:
1. How to determine the total numerical value of a string
2. When a "Number or Value"  is determined, then what random string of 16 characters (Hexadecimal...(0 - F), that will produce that value.

Example:
If I have two numbers:
41289M and 80201BA32115
    1.  I would want the numerical value for each number string.
    2.  After "massaging" the numbers, it generates another Value.
    3.   This massaged value is then used to produce a random 16 digit hexadecimal number that equals the value from step 2.

Cook09
Cook09Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Assuming A1 has
41289M

Then in B1 you can apply this..

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

Open in new window


Enclosed workbook for your reference...

Also just to add a VBA solution will be much more simpler by that i mean writing a UDF..let me know if you are interested in the same...

Saurabh...
Getnumber.xlsx
0
Cook09Author Commented:
Saurabh,

When I look at the workbook, it doesn't seem to provide what I requested.  For instance 41289M would probably equal 66.  Cell B2 shows 41289.

A UDF would be fine, especially if it meets the criteria listed above.

cook09
0
Saurabh Singh TeotiaCommented:
Cook09,

Can you help me understand how come 41289 = 66?? I dont understand the logic connection between two..

Saurabh...
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Cook09Author Commented:
Yes,
41289M 4+1+2+8+9+22 (M=22) ... However I was wrong... M=32 if A = 10, B=11, etc. An M wouldn't normally be there.  I need to go and find out how they use it.  Although, the other number follows hexadecimal notation (0 - 9, then A= 10, B =11...F=16).
So, 80201BA32115 = 8 +0+2+0+1+11+10+3+2+1+1+5 = 44

Cook09
0
Saurabh Singh TeotiaCommented:
I designed a UDF for you for what you are looking where...

However if A=10 then M=22 only...also enclosed is the file and UDF for your reference for what you are looking for..

Function getvalues(rng As Range)
    Dim i As Long, z As Long

    For i = 1 To Len(rng.Value)

        If IsNumeric(UCase(Mid(rng.Value, i, 1))) Then
            getvalues = getvalues + (Mid(rng.Value, i, 1) * 1)
        Else
        z = Asc(UCase(Mid(rng.Value, i, 1))) - 55
            getvalues = getvalues + z
        End If

    Next i
End Function

Open in new window


Excel file...

Saurabh...
getvalues.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cook09Author Commented:
Yes, that was what #1 was asking for...Part 2 asked about producing a 16 digit hexadecimal string with a value provided, i.e. 76 = what 16 digit random number that only displays 0-F.

Cook09
0
Saurabh Singh TeotiaCommented:
Cook09,

I'm not sure i follow you here..What you want to do in part-2? Can you give me more context or clarity about the same.?

Saurabh...
0
Cook09Author Commented:
Saurabh,
If I want a number that equals 65, then that value would translate into any number of 16 digit Hexadecimal characters (0-F). The 16 digit string 80201BA30A910532 equals the number 65.  But, as you can see any number of characters could have been used to satisfy the 65 value...that's why a random 16 digit string that equals a particular number is desired.

cook09
0
Saurabh Singh TeotiaCommented:
Cook 09,

I will keep that as a separate then this question as if you notice your part-2 in itself is a complete question in it which required to write a fresh program in itself. Also i'm not sure about how do you want to call out that process so if i understand correctly this is what you want to do..

From Hexadecimal you want to convert it to number Part-1 and Part-2 will be from a number to Hexadecimal ...

And currently i have solved part-1..Now you want to solve a Part-2 which basis of my assessment will be a separate question in itself  as it involves complex code of logic to be written for it rather then part of a same question...
0
Cook09Author Commented:
Saurabh,

I can live with that, they are two separate items, which were blended into one topic.  I'll close this and open a new question.

Thanks....

cook09
0
Cook09Author Commented:
Excellent
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.