We help IT Professionals succeed at work.

Excel - Formula or code to determine a random Hexadecimal string based on a particular value

Cook09
Cook09 asked
on
In the attached workbook, a UDF was coded to generate a numerical value, based upon a character string.  Part 2 would actually be the reverse.  If one has a numerical value, then what random 16 character hexadecimal string would = a numeric value.  This doesn't have to be directly related to the values generated within the UDF, in fact, it shouldn't be.  The reason for wanting a random 16 character hexadecimal string is that any number of 16 character strings could equal a particular value.

For example:
If there is numerical value 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 can be seen, 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
getvalues-from-Number.xlsm
Comment
Watch Question

Here is one solution

Function getstring(rng As Range)
    Dim i As Integer
    Dim n As Integer
    Dim res As Integer

    Application.Volatile
    res = rng.Value
    For i = 1 To 15
        n = Int(Rnd() * WorksheetFunction.Min(16, res))
        getstring = getstring & IIf(n < 10, n, Chr(n + 55))
        res = res - n
    Next i
        n = res
        getstring = getstring & IIf(n < 10, n, Chr(n + 55))
End Function

Author

Commented:
Hi Sayed,
I think this is close to what I'm looking for.  However, in attempting a value of 148.  I get a string, 115208A8D13A752 , and the 16th character is unrecognizable, so there is only 15.  Plus, it also  generated a string, 74DC4A4105C4374q.  I would like the letters only to be A-F, and Capitalized.  The out of context letters or characters seem to only appear on the 16th character.

Thanks,
cook09
Now try this. I have also modified it to allow to change the length of string as the second argument of the function. So now it would be something like    =getstring(A1,16)

Function getstring(rng As Range, strlen As Integer)
    Dim i As Integer
    Dim n As Integer
    Dim res As Integer

    Application.Volatile
    res = rng.Value
    If strlen * 15 < res Then
        getstring = "#Overflow"
        Exit Function
    End If
    For i = 1 To strlen
        avl = res - 15 * (strlen - i)
        If avl < 1 Then
            n = Int(Rnd() * WorksheetFunction.Min(strlen, res))
        Else
            n = Rnd() * (15 - avl) + avl
        End If
        getstring = getstring & IIf(n < 10, n, Chr(n + 55))
        res = res - n
    Next i
End Function

Author

Commented:
Yes, This seems to work....if I run into issues later, then I'll respond back...

Thank You..

Cook09
Any time