VBA：如何只保留一个字符串的日期值？ [英] VBA: How can I keep only the date values from a string?

问题描述

我有一个以下的字符串，并且想知道如何从中提取日期值并将它们存储在单独的单元格中。

I have a below string and was wondering how can I extract only date values from it and store them in separate cells.

11AUG2016已更改gggqqq2i8yj 29SEP2016已删除tyijdg298 30SEP2016已添加，mkdjenb200 03OCT2016 zzxxddd4423 04OCT2016 jioi == ++ - 234jju 24OCT2016更新tuiomahdkj 10JAN2017更新zzzz T4123III 13JAN2017更新jukalzzz123 20JAN2017 iiiwwwaazz678uuh

11AUG2016 Changed gggqqq2i8yj 29SEP2016 Removed tyijdg298 30SEP2016 Added ,mkdjenb200 03OCT2016 zzxxddd4423 04OCT2016 jioi==++-234jju 24OCT2016 Updated tuiomahdkj 10JAN2017 Updated zzzz T4123III 13JAN2017 Updated jukalzzz123 20JAN2017 iiiwwwaazz678uuh

推荐答案

以下方法保留字符串格式 - 即日期写为字符串（它使用简单的正则表达式）。
假设：你的字符串写在单元格A1中。

Following approach retains the string format - i.e. date is written as string (it uses a simple Regular Expression). Assumption: your string is written in cell A1.

Sub ExtractDateFromString()
    Dim s As String: s = Range("A1")
    Dim re As Object: Set re = CreateObject("VBScript.RegExp")
    re.Global = True
    re.Pattern = "(\d{2}[A-Z]{3}20\d{2}\s)"
    Set d = re.Execute(s)
    r = 2
    For Each x In d
        Range("A" & r) = x
        r = r + 1
    Next
End Sub

这篇关于VBA：如何只保留一个字符串的日期值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！