单元格区域首词/首两词提取及列文本特定内容提取技术咨询

Hey there! Let's break down your two text extraction needs step by step—super common scenarios when working with space-separated data, so I’ve got you covered.

提取第一个单词 #

If you just need the very first word before the first space, here are two solid options:

• For newer Excel versions (with dynamic arrays): Use TEXTSPLIT to split the text into an array, then grab the first element directly:

  =TEXTSPLIT(A1, " ")[1]
  

• For all Excel versions (including older ones): Use LEFT combined with FIND to get text up to the first space. Add IFERROR to handle cells with only one word (no spaces):

  =IFERROR(LEFT(A1, FIND(" ", A1)-1), A1)
  

提取第一个 + 第二个单词 #

To grab the first two words (joined with a space):

• Newer Excel: Split the text, take the first two elements, then join them back:

  =TEXTJOIN(" ", TRUE, TEXTSPLIT(A1, " ")[1:2])
  

• Older Excel: Find the position of the second space, then extract everything before it. Again, IFERROR handles cases with fewer than two words:

  =IFERROR(LEFT(A1, FIND(" ", A1, FIND(" ", A1)+1)-1), A1)
  

First off—your idea of reversing the word order and removing the first two elements is totally valid! Because in both your cases, the last two items are numeric/technical suffixes, and you want everything before that. Let's turn that idea into working formulas, plus some simpler alternatives.

Context recap #

情况1（3 words, 2 spaces）: BALDOR 3 hp-4 → Target: BALDOR
情况2（4 words, 3 spaces）: US ELECTRICAL 75 hp-232 → Target: US ELECTRICAL

Newer Excel (dynamic arrays supported) #

This is the cleanest approach—we can directly grab all words except the last two:

=TEXTJOIN(" ", TRUE, TEXTSPLIT(A1, " ")[SEQUENCE(COUNTA(TEXTSPLIT(A1, " "))-2)])

Let me break this down:

1. TEXTSPLIT(A1, " "): Splits your text into an array of individual words
2. COUNTA(...): Counts how many words there are; subtract 2 to exclude the last two
3. SEQUENCE(...): Generates a list of positions (1 to [total words - 2]) to grab the first N words we need
4. TEXTJOIN: Puts those words back together with spaces

If you want to stick strictly to your reverse-and-remove idea, here's how to implement it:

=TEXTJOIN(" ", TRUE, SORTBY(INDEX(TEXTSPLIT(A1, " "), SEQUENCE(COUNTA(TEXTSPLIT(A1, " "))-2, 1, 3)), SEQUENCE(COUNTA(TEXTSPLIT(A1, " "))-2), -1))

This reverses the word list, skips the first two elements, then reverses the remaining list back to original order. It works, but the first formula is more efficient.

Older Excel (no dynamic arrays) #

We'll use nested text functions to find the position of the second-to-last space, then extract everything before it:

=LEFT(A1, FIND("~", SUBSTITUTE(A1, " ", "~", LEN(A1)-LEN(SUBSTITUTE(A1, " ", ""))-1))-1)

Or for readability, use helper columns:

1. Helper Column B (count spaces): =LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
2. Helper Column C (position of second-to-last space): =FIND("~",SUBSTITUTE(A1," ","~",B1-1))
3. Target Column D: =LEFT(A1,C1-1)

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