☰

如何在 Excel 表上创建字母数字用户 ID？

tag-icon • tag-icon microsoft-excel worksheet-function
如何在 Excel 表上创建字母数字用户 ID？

用户 ID 必须包含用户名字和姓氏的前三个字母以及随机数，如下所示 -

User id             Fname   Lname       Langu
AA_RoeDuT449        Roelof  DuToit       en
AA_IsmBha732        Ismail  Bhamjee      en

用户一定是这样的。

有人能帮我使用公式来得到这种类型的 ID 吗？非常感谢

答案1

这是 VBA 版本。公式对此来说确实是一个糟糕的选择，因为您需要将固定的“RandomID”存储在某处。此外（在这种情况下可能并不重要），但如果某人的姓名发生变化（婚姻或类似情况），则会更改其 UserID。此解决方案提供了一个固定的输出，当您修改工作簿时它不会更改，并且只有在您手动更改单元格时才会更改。它还会检查 UID 的设置是否唯一。

Public Sub GenerateUserIDs()
    'Get a quantity of rows by checking B2 for the last non empty cell. Add 1 because we have a header and
    'it won't be included
    NumRows = Range("B2", Range("B2").End(xlDown)).Rows.Count + 1
    'Loop through all our rows, starting at 2
    For X = 2 To NumRows
        'Check each row, if column A is empty
        If IsEmpty(Range("A" & X)) Then
            Dim FName, LName, UID, ProposedUID, MaxNumberOfLoops, NumberOfLoops

            'If it is, get their first and last name
            FName = Range("B" & X).Value
            LName = Range("C" & X).Value

            'Set a maximum number of loops (If it loops more than this, quit, or we have an infinite loop
            'and Excel will crash)
            MaxNumberOfLoops = 400
            NumberOfLoops = 0

            'Set the UID without the random number
            UID = "AA_" & Left(FName, 3) & Left(LName, 3)
            'Keep looping until we find a UID that doesn't already exist
            Do
                'Add a loop count
                NumberOfLoops = NumberOfLoops + 1
                'Check if we've exceeded the loops
                If NumberOfLoops > MaxNumberOfLoops Then
                    'We have. Message box and quit!
                    MsgBox "Exceeded " & MaxNumberOfLoops
                    Exit Sub
                End If
                'Add the Random Number to our UID before testing if it exists
                ProposedUID = UID & RandomBetween(100, 999)
            Loop While CheckUIDExists(ProposedUID, NumRows)

            'We're out of the loop, so our UID is indeed unique. Set it in column A of the current row
            Range("A" & X).Value = ProposedUID
        End If
    'On to the next row!
    Next
End Sub

Function RandomBetween(Low As Long, High As Long)
    'Randomize the numbers (must be run to get a truly random number'
    Randomize
    'Get a number between our highest and lowest
    RandomBetween = Int((High - Low + 1) * Rnd + Low)
End Function

Function CheckUIDExists(ProposedUID, NumRows)
    'Again loop through all our rows
    For i = 2 To NumRows
        'Check if column A contains our proposed UID
        If Cells(i, 1).Value = ProposedUID Then
            'If it does, send True back, indicating it needs to run again
            CheckUIDExists = True
            'Quit early because we don't need to test any more rows
            Exit Function
        End If
    'On to the next row...
    Next i
    'We've done the entire loop, so it doesn't exist, return a False, indicating we can use that UID
    CheckUIDExists = False
End Function

只要 UserID 中没有任何内容，它就会自动填充给你

在此处输入图片描述

运行后：

在此处输入图片描述

答案2

尝试使用concatenate，left如下randbetween所示：

=CONCATENATE(LEFT(C2;3); LEFT(D2;3); RANDBETWEEN(100;999))

相关内容