如何在Excel中用单元格文本首字符生成唯一标识？

Perfect, let's figure out how to pull the first letter of each word from an Excel cell and mash them into a unique identifier like LLBB. I'll cover a few methods depending on which Excel version you're using—no fancy external tools needed!

This is the cleanest approach for modern Excel versions, leveraging dynamic array functions to handle any number of words automatically.

Use this formula in cell A2:

=TEXTJOIN("", TRUE, MID(UPPER(A1), SEARCH(" ", A1&" ", SEQUENCE(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1)), 1))

Let's break down what each part does:

• LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1: Counts how many words are in A1 (subtracting the length of the cell with spaces removed from the original length gives the number of spaces; add 1 to get word count)
• SEQUENCE(...): Generates a list of numbers from 1 to the word count, which helps target each word's starting position
• SEARCH(" ", A1&" ", SEQUENCE(...)): Finds the position right after each space (adding a trailing space to A1 ensures we catch the last word too)
• MID(UPPER(A1), ..., 1): Extracts the first character of each word and converts it to uppercase (remove UPPER() if you want lowercase)
• TEXTJOIN("", TRUE, ...): Glues all the extracted letters together, ignoring any empty values (handy if there are accidental extra spaces)

For your example where A1 is "lorem lipsum bla bla", this will spit out LLBB exactly as you need.

If you're stuck on an older Excel version (pre-2019), use this array formula. After typing it in, press Ctrl + Shift + Enter instead of just Enter to activate it:

=CONCATENATE(MID(UPPER(A1), SEARCH(" ", A1&" ", ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1))), 1))

This works similarly to the first method, but uses ROW(INDIRECT(...)) to generate the sequence of positions instead of SEQUENCE(), and CONCATENATE() to join the letters.

Alternatively, if you need to handle cases with multiple consecutive spaces, use this array formula to filter out empty entries:

=CONCAT(IF(MID(A1&" ", SEARCH(" ", A1&" ", ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1))), 1)<>"", UPPER(MID(A1&" ", SEARCH(" ", A1&" ", ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1))), 1)), ""))

Again, remember to press Ctrl+Shift+Enter after entering this.

If you find yourself needing this often, a custom VBA function is the most flexible solution—it handles edge cases like multiple spaces, empty words, and even non-space delimiters easily.

1. Press Alt + F11 to open the VBA Editor
2. Right-click your workbook in the Project Explorer > Insert > Module
3. Paste this code into the module:

Function GetFirstLetters(inputText As String) As String
    Dim words() As String
    Dim result As String
    Dim i As Integer
    ' Split the text into words using space as delimiter
    words = Split(inputText, " ")
    result = ""
    ' Loop through each word to grab the first letter
    For i = LBound(words) To UBound(words)
        ' Skip empty entries from multiple spaces
        If Trim(words(i)) <> "" Then
            result = result & UCase(Left(Trim(words(i)), 1))
        End If
    Next i
    GetFirstLetters = result
End Function

4. Close the VBA Editor
5. Back in Excel, use the function like this in cell A2:

=GetFirstLetters(A1)

This will handle messy input like "lorem lipsum bla bla" (with multiple spaces) and still return LLBB.

Quick Notes #

• If your words are separated by something other than spaces (like commas), just replace the " " in the formulas or VBA code with your delimiter (e.g., ",").
• Remove the UCASE() part if you want lowercase letters instead of uppercase.

内容的提问来源于stack exchange，提问作者Marshall Telaumbanua