☰

Excel 函数/宏从逗号分隔列表中提取数字

tag-icon • tag-icon microsoft-excel worksheet-function macros
Excel 函数/宏从逗号分隔列表中提取数字

我正在尝试使用 excel 函数从逗号分隔列表中提取数字

数据如下：

1,58,,私人信息 #1,59 秒后，您私人估计最终价值将为 44.17 美元

如果上述数据在单元格 A1 中，我想在单元格 B1 中检索 59，在单元格 C1 中检索 44.17。数字的位数可能会有所不同。只需要第 5 和第 6 个逗号分隔字段中的数字，并且每个字段中只有一个数字。对于第 5 段，# 将始终被相同的文本包围（即，xxx 秒后）。对于第 6 段，它将始终是最后一个字符，并且它们始终跟在单个 $ 后面

我不熟悉 Excel，希望得到任何帮助！提前致谢。

答案1

公式为C1：

=VALUE(MID(A1,SEARCH("$",A1,FIND("|",SUBSTITUTE(A1,",","|",4)))+1,LEN(A1)))

假设第六段中只有一个美元符号。
可以将条形符号更改为任何保证不在文本中的字符。MID
允许返回比实际更多的字符（仅返回文本的其余部分；一种偷懒的 RIGHT，不是计算要返回的字符数，而是使用起始位置）。

公式为B1：

=VALUE(MID(A1,SEARCH("after",A1,FIND("|",SUBSTITUTE(A1,",","|",4)))+5,SEARCH("second",A1,SEARCH("after",A1,FIND("|",SUBSTITUTE(A1,",","|",4)))+5)-(SEARCH("after",A1,FIND("|",SUBSTITUTE(A1,",","|",4)))+5)))

B1 通过辅助单元格 (D1) 进行简化：

D1=SEARCH("after",A1,FIND("|",SUBSTITUTE(A1,",","|",4)))+5 B1=VALUE(MID(A1,D1,SEARCH("second",A1,D1)-D1))

答案2

要得到59和44.17，我建议将 UDF（VBA 宏）与公式结合起来。

在此处输入图片描述

怎么运行的：

  • 在 中写入文本A1。

  • 将此 VBA 代码复制并粘贴为带有工作表的模块。

    Function INDEXLIST(strList As String, strSeparator As String, lngIndex As Long) As String

Dim ListArray() As String
ListArray() = Split(strList, strSeparator)
INDEXLIST = ListArray(lngIndex - 1)

End Function

  • 在B1&中写入&B2的搜索位置为59& 。44.175th6th

  • 将此公式写入C1并填写。

=INDEXLIST(A1,",",B1)

  • 最后输入这个公式D1并填写下来。

    =LOOKUP(9.9E+307,--LEFT(MID(C1,MIN(FIND({1,2,3,4,5,6,7,8,9,0}, $C1&"1023456789")),999),ROW(INDIRECT("1:999"))))

  • 你得到59&44.17。

注意：

  • 9.9E+307是 Excel 可以在单元格中存储的最大数字。通常称为Biggest Number。

  • LOOKUP 尝试查找小于或等于查找值的最大值。如果查找值为9.9E+307，则不可能有大于9.9E+307范围的数字，因此查找将查找范围内的最大值。

    FIND({1,2,3,4,5,6,7,8,9,0}

  • 当在范围中找到与 ROW 函数返回的值数组匹配的数据时，该FIND函数返回数据的位置{1,2,3,4,5,6,7,8,9,0}。

  • 999设置最大 3 位数字，并且 Row(INDIRECT("1:999")` 指的是数据单元。

  • 根据需要调整公式中的单元格引用。

相关内容