在VBA中搜索包含小数的数字的字符串 [英] Searching a string for numbers including decimals in VBA

问题描述

所以我正在从一个相当笨重的数据库中输入的项目进行工作，我对它所提供的数据类型没有任何控制。它基本上给我一个字符串，其中包含数字，包括小数位。

So I'm working on a project that has inputs from a fairly clunky database that I have zero control over what type of data it gives me. It basically gives me a string that has numbers in it including decimals.

采用0.5标签 每天。

"take 0.5 Tab by mouth 2 times daily."

每当它显示标签时，我想抓住标签之前的数字并将其转换为双格式。我知道如何使用cdbl转换它，一旦我有字符串0.5，但是如何得到只是那个字符串是一种困难，因为InStr只搜索从左到右。我的想法是使用InStr来找到tab之前的数字之前的空格，但我无法弄清楚如何编写代码。任何建议？

Whenever it says tab I want to grab the number before tab and convert it to double format. I know how to use cdbl to convert it once I have the string "0.5" but how I get just that string is kind of difficult since InStr only searches left to right. My thought was to use InStr to find the space before the number that comes before the word "tab" but I'm having trouble figuring out how to code it. Any suggestions?

推荐答案

InStrRev 从右到左进行搜索。或者，您可以使用 StrReverse 并使用输出，但我将使用 VBScript.Regexp ：

InStrRev searches right to left. Alternatively, you can use StrReverse and work with the output, but I would use VBScript.Regexp:

Dim text As String
text = "take 0.5 Tab by mouth 2 times daily"

Dim regex As Object
Set regex = CreateObject("VBScript.Regexp")

regex.Global = True
regex.Pattern = "[\d\.]+(?=\sTab)"

Dim test As Object
Set test = regex.Execute(text)
MsgBox (test(0).Value)

这篇关于在VBA中搜索包含小数的数字的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！