Excel VBA-提取字符串中的数值 [英] Excel VBA - Extract numeric value in string

问题描述

我有多个数据表，其中的一列中包含有关各种合同价值的信息.对于每个合同，该列每个单元格中包含的信息显示如下:

I have multiple tables of data in which, in one column, I have information about the value of various contracts. For each contract, the information contained in each cell of that column is presented as follows:

合同价值为$ XX，XXX.XX."

"The value of the contract is $XX,XXX.XX."

有时，在美元值之后还会有其他文本，如下所示:

Sometimes, there will be additional text after the dollar value, as follows:

合同价值为$ XX，XXX.XX.可以选择修改合同期限"

"The value of the contract is $XX,XXX.XX. There is an option to modify the duration of the contract"

我需要编写一个子程序，使我可以在该文本字符串中提取美元价值，并且仅保留该信息(并且没有任何文本出现在该文本的前后).

I need to program a sub that allows me to extract the dollar value within that string of text and only keep that information (and none of the text coming before and after).

我在这里面临的困难是，一切都可能会发生变化.美元价值永远不会相同，并且其前后的文字也会发生变化.

The difficulty I'm facing here is that everything is subject to change. The dollar value is never the same and the text before or after it also changes.

到目前为止，使用SPLIT函数和$作为分隔符，我已经能够成功保留$符号之后的所有内容.但是，我一直无法删除可能跟随美元价值的任何文本.

So far I've been able to successfully keep everything after the $ sign, using the SPLIT function and $ as delimiter. However, I keep having problems removing whatever text may follow the dollar value.

关于我如何进行的任何想法?

Any idea on how I could proceed?

谢谢您的帮助！

推荐答案

以防万一，它很简单-您可能实际上不需要子程序.这样的公式:

Just in case its easier - you might not actually need a sub. A formula such as this:

=VALUE(LEFT(MID(B3,FIND("$",B3)+1,LEN(B3)),FIND(".",B3)-FIND("$",B3)+2))

适用于此示例:

这篇关于Excel VBA-提取字符串中的数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！