# 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
###### 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.

Commented:
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)
``````

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
Author 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
Commented:
Cook09,

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

Saurabh...
Author 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
Commented:
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
``````

Excel file...

Saurabh...
getvalues.xlsm

Experts Exchange Solution brought to you by

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

Author 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
Commented:
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...
Author 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
Commented:
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...
Author 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
Author Commented:
Excellent
###### 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.