1

I would to like to remove all characters that have this format: XX/XX@-@XX:XX@-@, where X could be any number.

I try to resolve that with this program:

Sub Date_Time()
'
' It will looking for "/" and delete 16 characters, starting 3 characters before "/".
' It should remove all Date and Time in the file.
'
With Sheets("Get_Command")
.Select

Lastrow = .UsedRange.Rows.Count

    For Lrow = 1 To Lastrow Step 1

        Set find2 = Cells(Lrow, 1).Find("/", LookIn:=xlValues) ' Look for "/"

        If Not find2 Is Nothing Then

            Cells(Lrow, 17).FormulaR1C1 = "=SEARCH(""/"",RC[-16])"
            Cells(Lrow, 18).FormulaR1C1 = "=MID(RC[-17],RC[-1]-2,RC[-1]+13)"

            Cells(Lrow, 1).Replace _
                What:=Cells(Lrow, 18).Value, Replacement:="", _
                LookAt:=xlPart, SearchOrder:=xlByColumns


        End If

    Next Lrow

Columns("Q:R").Select
Selection.Delete Shift:=xlToLeft

End With

End Sub

The program is, it is looking just for the first "/" in the row and could have some "/" that it is not in the format that I want to remove.

Someone have some idea how can I fix it?

Thanks,

Community
  • 1
  • 1
Li_DE
  • 39
  • 6
  • 1
    I'm not sure if I completely understand, but if you're looking for something with such a specific pattern, I'd suggest using Regular Expressions for doing the replacing.... – John Bustos Mar 18 '14 at 14:51
  • Here is a [link](http://www2.hawaii.edu/~chenx/reading/VBA/vb_regexp.html) to Regex operations in VBA. – Automate This Mar 18 '14 at 14:59
  • I didn't understand the RE, what I'm lookink for in the rows is the date and time, and it will show always in the same format but not necessary the same day and time. I don't know if I was clear enough! – Li_DE Mar 18 '14 at 15:20

1 Answers1

0

I believe the Regex expression your looking for is:

[0-9]{2}\/[0-9]{2}\@\-\@[0-9]{2}:[0-9]{2}\@\-\@

Here is a generic function taken from here:

Function RegExpReplace(ByVal WhichString As String, _
                        ByVal pattern As String, _
                        ByVal ReplaceWith As String, _
                        Optional ByVal IsGlobal As Boolean = True, _
                        Optional ByVal IsCaseSensitive As Boolean = True) As String
    'Declaring the object
    Dim objRegExp As Object

    'Initializing an Instance
    Set objRegExp = CreateObject("vbscript.regexp")

    'Setting the Properties
    objRegExp.Global = IsGlobal
    objRegExp.pattern = pattern
    objRegExp.IgnoreCase = Not IsCaseSensitive

    'Execute the Replace Method
    RegExpReplace = objRegExp.Replace(WhichString, ReplaceWith)

End Function

Test sub looks like this:

Sub testRegex()
    Dim str As String
    str = "My 11/22@-@11:22@-@ test"

    MsgBox (str)

    Dim pattern As String
    pattern = "[0-9]{2}\/[0-9]{2}\@\-\@[0-9]{2}:[0-9]{2}\@\-\@"

    MsgBox (RegExpReplace(str, pattern, ""))
End Sub

Results: first message box shows the unaltered string

enter image description here

The last message box shows the string with the pattern removed that matched the regex

enter image description here

Automate This
  • 30,726
  • 11
  • 60
  • 82
  • Thanks for the perfect answer! =) I'm going to study what did you show and see if I really got the idea! I tought that I was getting use with programming but maybe not! hahahaha – Li_DE Mar 18 '14 at 15:29
  • Here is a post that can help provide you with more detail on [Regex for Excel](http://stackoverflow.com/q/22542834/2521004). – Automate This Mar 24 '14 at 14:21