Geek Answers Handbook

Excel 2007 - Create Unique Identifier Based on Text?

I have a sheet with a list of names in column B and column ID in A. I was wondering if there is any formula that can take a value in column B of this row and generate some sort of identifier based on the text? Each name is also unique and never repeated.

It would be better if I did not have to use VBA. But if I must, so be it.

+6
source share
6 answers

Solution without VBA.

The logic is based on the first 8 characters + the number of characters in the cell.

= CODE(cell)which returns the code number for the first letter

= CODE(MID(cell,2,1)) returns the code for the second letter

= IFERROR(CODE(MID(cell,9,1)) If the 9th character does not exist, return 0

= LEN(cell)

8 +

8 , .

:

=CODE(B2)&IFERROR(CODE(MID(B2,2,1)),0)&IFERROR(CODE(MID(B2,3,1)),0)&IFERROR(CODE(MID(B2,4,1)),0)&IFERROR(CODE(MID(B2,5,1)),0)&IFERROR(CODE(MID(B2,6,1)),0)&IFERROR(CODE(MID(B2,7,1)),0)&IFERROR(CODE(MID(B2,8,1)),0)&LEN(B2)

enter image description here

+2

, , ( ), , .

, , UDF ( ):

:

Public Function genId(ByVal sName As String) As Long
'Function to create a unique hash by summing the ascii value of each character of a given string
    Dim sLetter As String
    Dim i As Integer
    For i = 1 To Len(sName)
        genId = Asc(Mid(sName, i, 1)) * i + genId
    Next i
End Function

, :

=genId(A1)

[EDIT] * i, .

+1

OTT , CoCreateGuid, GUID

Private Declare Function CoCreateGuid Lib "ole32" (ID As Any) As Long

Function GUID() As String
    Dim ID(0 To 15) As Byte
    Dim i As Long

    If CoCreateGuid(ID(0)) = 0 Then
        For i = 0 To 15
            GUID = GUID & Format(Hex$(ID(i)), "00")
        Next
    Else
        GUID = "Error while creating GUID!"
    End If

End Function


Sub testGUID()
    MsgBox GUID
End Sub

, . - , GUID, , . ( , udf , , GUID )


. SHA1

0

, ? , , .

B, B2 , , A2, "= IF (B2 =" "," ", 1 + MAX (A $1: A1))", , , B, .

- , , "" , . , , , , .

0

. .

=LEN($J$14)-LEN(SUBSTITUTE($J$14;"a";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"e";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"i";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"j";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"o";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"u";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"y";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"1";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"2";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"3";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"4";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"5";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"6";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"7";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"8";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"9";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"0";""))
0

, , . , , 8 ?

, . , ASCII- - 40 [ , 8 57 57 122] 10 ^ [ ]. [-40] .

EDIT , , 8 , , , , 8 "0".

=TEXT(SUM((CODE(MID(LOWER(RIGHT(REPT("0",8)&A3,8)),{1,2,3,4,5,6,7,8},1))-40)*10^{0,2,4,6,8,10,12,14}),"#")

, ASCII , ID - , 8 . -40, "". -40 - , 10 ^ 0,2,4 .. 2- .

, , , , 10 * [ ]. (. ) , 10 ^ 26-1, Excel. :

, 10 ^ 15-1, Excel . :

=RIGHT(REPT("0",15)&TEXT(SUM(LEN(A3)*10^{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14}-LEN(SUBSTITUTE(A3,MID(Alphabet,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15},1),""))*10^{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14}),"#"),15)

[ ( "00000000000000"... , ]

, - , : "abcdehilmnorstu". , , "asdf" a, s d, "f", . "asdf":

001000000001001

:

, ( / ), , . , asdf asd .

, . , asd dsa .

0

More articles:


All Articles