在VBA中定义InStr

Question

在他们更改日期/时间格式之前，有一行代码可以正常工作。我需要从中提取日期、小时和分钟:

StringOfText: StringOfText1开始运行，result: XXXXXXXX 2020-01-21_11h49m38s

这就是我正在使用的，但是在运行excel宏时出现错误不匹配:

Start_Time = _
DateValue(Left(Right(Left(Cells(i, x).Value, (InStr(Cells(i, x).Value, "m*s") + 2)), 20), 10)) + _
TimeValue(Left(Right(Left(Cells(i, x).Value, (InStr(Cells(i, x).Value, "m*s") + 2)), 9), 2) & _
":" & Left(Right(Left(Cells(i, x).Value, (InStr(Cells(i, x).Value, "m*s") + 2)), 6), 2))

我知道我需要一个"喜欢"但是当我加进去的时候，我得到了更多的错误。代码在"m* "有"min"它在之前使用的行的末尾。I和x是在这段代码上面预定义的。

任何想法或帮助将不胜感激。

Answer 1

如果你的日期时间总是在字符串的最后，那么使用分割和替换将返回日期/时间:

Sub kljl()
Dim str As String
str = "StringOfText1 beginning run, result: XXXXXXXX 2020-01-21_11h49m38s"

Dim strArr() As String
strArr = Split(str, " ")

Dim dte As Date
dte = CDate(Replace(Replace(Replace(Replace(strArr(UBound(strArr)), "_", " "), "h", ":"), "m", ":"), "s", ""))

Debug.Print dte
End Sub

---

如果它不总是在末尾，那么我们可以循环strArray并找到第一个日期/时间

Sub kljl()
Dim str As String
str = "StringOfText1 beginning run, result: XXXXXXXX 2020-01-21_11h49m38s"

Dim strArr() As String
strArr = Split(str, " ")

Dim i As Long
i = 0

Dim dte As Date
dte = 0

Do
On Error Resume Next
dte = CDate(Replace(Replace(Replace(Replace(strArr(i), "_", " "), "h", ":"), "m", ":"), "s", ""))
On Error GoTo 0
i = i + 1
Loop Until dte > 0

Debug.Print dte
End Sub

Answer 2

dim s as string

'this is the cell value in your loop
s = Cells(i, x).Value

Dim i As Long

'locate as a reference point the underscore as a known, reliable anchor
i = InStr(1, s, "_", vbTextCompare)
'Once we know where the data is we can remove the formatting from the string
' that prevents us from using VBA CDate - "_", "h", "m", "s"
' use mid string with no third parameter to get all of the remaining string from the start point
' use len("yyyy-mm-dd") rather than just 10 as a magic number for clarity
s= Mid(s, i - len("yyyy-mm-dd"))
' make necessary replacements to change "2020-01-01_11h20m34s" to
' 2020-01-01 11:20:34" which can be parsed by CDate
s = Replace(s, "_", " ")
s = Replace(s, "h", ":")
s = Replace(s, "m", ":")
s = Replace(s, "s", "")
start_time=CDate(s)