文本提取数字

1.数字在文本开始处

-LOOKUP(0,-LEFT(要提取数字所在单元格，ROW($1:$15)))

ROW($1:$15),数字15表示想要提取数字的最长长度，根据实际情况调整

Excel单元格提取数字和分列拆分-趣帮office教程网

上述例子中，公式：=-LOOKUP(0,-LEFT(A3,ROW($1:$15)))

2.数字在文本结尾处

-LOOKUP(0,-RIGHT(要提取数字所在单元格，ROW($1:$15)))

Excel单元格提取数字和分列拆分-趣帮office教程网

上述例子中，公式：-LOOKUP(0,-RIGHT(A7,ROW($1:$15)))

3.数字在文本中间

=-LOOKUP(1,-RIGHT(LEFT(要提取数字的单元格,LOOKUP(10,--MID(要提取数字的单元格,ROW($1:$20),1),ROW($1:$18))),ROW($1:$20)))

Excel单元格提取数字和分列拆分-趣帮office教程网

=-LOOKUP(1,-RIGHT(LEFT(B3,LOOKUP(10,--MID(B3,ROW($1:$20),1),ROW($1:$20))),ROW($1:$20)))

  1. MID(B3,ROW($1:$20),1)：这个函数将单元格 B3 中的文本逐个字符提取出来，ROW($1:$20) 返回一个数组 {1,2,3,...,20}，表示行号 1 到 20。这样就可以逐个提取 B3 中的字符。
  2. --MID(...)：双重否定运算符 -- 用于将提取的字符转换为数值。如果提取的字符是数字，则结果保持不变，如果不是数字，则会转换为0。
  3. LOOKUP(10,--MID(...),ROW($1:$20))：这个函数查找数组中值为10的元素，并返回该值所在位置的行号。因为我们之前将字符转换为数值并且提取的是右侧数字，这里实际上是在查找从右向左数第一个数字所在的位置。
  4. LEFT(B3,...)：LEFT 函数用于获取 B3 单元格左侧的文本内容，LOOKUP(10,...) 返回的位置作为参数传递给 LEFT 函数，以确定要截取的文本范围。
  5. RIGHT(...,ROW($1:$20))：RIGHT 函数用于从 LEFT 函数返回的文本中提取最右侧的数字。ROW($1:$20) 返回数组 {1,2,3,...,20}，确定要提取的字符数。
  6. LOOKUP(1,-RIGHT(...))：LOOKUP 函数在提取的最右侧数字中查找值为1的元素，并返回该值。因为我们提取的是最右侧的数字，所以这里实际上是提取最右侧的数字。

分列拆分单元格内容

TRIM、MID、SUBSTITUTE、REPT、LEN 等文本函数提取分列后的第 n 个文本，填入对应的单元格。

Excel单元格提取数字和分列拆分-趣帮office教程网

=TRIM(MID(SUBSTITUTE($B3,"-",REPT(" ",LEN($B3))),(COLUMN(A$1)-1)*LEN($B3) 1,LEN($B3)))

  1. SUBSTITUTE($B3,"-",REPT(" ",LEN($B3)))：这个函数将单元格 $B3 中的所有“-”替换为空格，使用 REPT 函数生成与单元格 $B3 相同长度的空格字符串作为替换文本。这意味着如果 $B3 包含 3 个字符，则将使用 3 个空格来替换每个“-”。
  2. MID(...,(COLUMN(A$1)-1)*LEN($B3) 1,LEN($B3))：这个函数使用 MID 函数从替换后的字符串中提取一个子字符串。COLUMN(A$1) 函数返回 A1 列的列号，所以 (COLUMN(A$1)-1) 的结果是 0。这意味着第一列使用偏移 0 来提取子字符串，即从字符串的第一个字符开始。LEN($B3) 返回 $B3 的字符数，因此函数提取长度为 $B3 字符数的子字符串。
  3. TRIM(...)：最后，TRIM 函数用于删除提取的子字符串的前导和尾随空格。这是必要的，因为如果单元格中的第一个字符是空格，则 MID 函数会提取该空格并将其包含在内。

整个公式是一个数组公式，可以通过拖动填充手柄或按 Ctrl Shift Enter 来应用到整个区域。通过这种方式，公式将自动调整列偏移量以从每列中提取正确的子字符串。