我需要从 excel 单元格中的字符串中提取最后 7 个数值(不是字符)。该字符串包含一个文件名(可以是字母、数字和破折号的混合),然后是需要移动到另一列的 7 个数字。
例如Johnson Set1- 0 0 1 14 9 54 0
数字值从 0 到 999 不等,因此无法仅提取最后 7 个字符。
答案1
因此,您可以使用一些技巧来获得您想要的东西。我会尽力在这里解释它们,然后给您一个使用这些技巧来满足您的需求的公式。
关键是找到数字列表开始前的空格。如果您的示例足够通用,则该空格将始终是右侧第 7 个空格。问题是右侧第 7 个空格可能是左侧的任何数字空格,因为听起来文件名没有固定数量的空格规则。并且该SUBSTITUTE
函数允许您指定要替换字符串的哪个实例(从左侧开始)。
要得到从右边算起的第 7 个空格,我们必须将其转换为从左边算起的空格。为此,我们需要知道整个字符串中的空格数。一旦知道了,就只需要做减法了。
要查找字符串中的空格总数,有一个使用SUBSTITUTE
和LEN
函数的技巧。这个想法是将字符串中的所有空格替换为空,然后将输出的长度与原始字符串的长度进行比较。差异当然是原始字符串中的空格数。因此,对于中的字符串A1
,以下将返回空格数:
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
因此,您想要的左侧空格数实例左侧始终会有 6 个空格。
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6
现在,下一个问题是 只SUBSTITUTE
允许您指定要更改的实例编号。FIND
,这对于提取数字来说最方便,但没有此功能。 那么诀窍就是使用SUBSTITUTE
来生成一个允许您随意使用的字符串FIND
。 如果您将右侧第 7 个空格替换为字符串中其他任何地方都没有出现的字符串,那么您将有一个唯一的子字符串可以搜索。 因此,例如,您可以使用以下内容将所需的空格替换为“REPLACE!ME”:
=SUBSTITUTE(A1," ","REPLACE!ME",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6)
现在,您只需要在字符串中找到“REPLACE!ME”的索引并返回其右侧的所有内容。这可以使用RIGHT
、LEN
和FIND
函数来完成。最后一个函数有点重复,但它可以完成工作。
=RIGHT(SUBSTITUTE(A1," ","REPLACE!ME",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6),LEN(SUBSTITUTE(A1," ","REPLACE!ME",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6))-FIND("REPLACE!ME",SUBSTITUTE(A1," ","REPLACE!ME",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6))-LEN("REPLACE!ME")+1)