Replace numbers using regular expressions

[techiewithinme]

Hello,

I have a date value in the below format and I want to replace all numbers after seconds part with blank. How do I do that using regular expression?

10/4/2011 01:10:11:000300000 AM

Thanks.

 

[Rick Rothstein]

Following up on GTO's sugeestion of using a UDF.

If GTO is correct in his suggestion, then we don't need the heavy artillery of a RegExp engine to do what the OP asked for... this one-liner UDF should do what he wants:

Function ModifyTime(DateExp As Variant) As String
  ModifyTime = Left(DateExp, InStr(DateExp, ":") + 5) & Right(DateExp, 3)
End Function

 

[GTO]

If GTO is correct in his suggestion, then we don't need the heavy artillery of a RegExp engine to do what the OP asked for... this one-liner UDF should do what he wants:

Hello,

I have a date value in the below format and I want to replace all numbers after seconds part with blank. How do I do that using regular expression?

10/4/2011 01:10:11:000300000 AM

Thanks.

Thanks for your help. Is there any way to replace it by using regular expressions (find and replace functionality) instead of formulas?

I found another easy formula...
But I want with regular expression. Is there any good source info using regular expression in excel?

Hi Rick

While a RegExp may be a bit much for what is being done; well, call me silly, but I suspect the OP may actually want to try RegExp.

techwithinme,

Here are some links:
http://www.aivosto.com/vbtips/regex.html
http://msdn.microsoft.com/en-us/library/1400241x(VS.85).aspx
http://www.regular-expressions.info/tutorial.html
http://msdn.microsoft.com/en-us/library/ms974570.aspx

Hope that helps,

Mark

 

[xenou]

Agreed that the OP asked for regex but it isn't clear if he meant using Find (which is a GUI dialog) or in a formula, if UDF's are ok, if he/she knew that other formulas could do the same job, or even if techie just wanted to know in what ways Excel supports regex. Unfortunately, both Find and Regex are terms that can have different contexts.

We will just have to wait and see if techie ever comes back as it's a moot point until then...

ξ

EDIT:
Though, Leith - I'm not familiar with this syntax in RegExp.Replace()
ModifyTime = re.Replace(DateExp, "$1 $2")
I'll look that up in the RegExp reference tonight unless you have time to explain it first ...
(I assume we are somehow dealing with the parenthetical groups in the regex pattern).

 

[Leith Ross]

Hello xenou,

You're correct about the parentheses. The string is parsed into 2 parts. The $1 and $2 represent the internal Submatches of the string. There go from left to right. The Replace function concatenates these submatches with a space between them if the pattern is found. If not then the original string is returned.

 

[xenou]

Excellent. I'm not very familiar with submatches in regex patterns - this looks like a good case where they can be useful. Thanks.

 

[techiewithinme]

Techie:
I just want to advise you that only forum regulars are allowed to post links in their signatures - a link in the post body that is not directly related to the to the post content falls under the same rule. See rule 4 here (a board regular is a member for at least three months and at least 100 posts)

xenou

Edit regarding regex it's not included in Excel so far as formulas and search dialogues go, I'm afraid.

thanks for making it clear xenou.

 

[Rick Rothstein]

Hi Rick

While a RegExp may be a bit much for what is being done; well, call me silly, but I suspect the OP may actually want to try RegExp.

Yes, I'm sure you are right. The point I should have made with my message is that, in my experience at least, rarely is the machinery and overhead of a RegExp engine needed to do parsing in Excel... VB's built-in string functions, coupled with the Like operator, are almost always more than enough to do the job.

 

[techiewithinme]

Agreed that the OP asked for regex but it isn't clear if he meant using Find (which is a GUI dialog) or in a formula, if UDF's are ok, if he/she knew that other formulas could do the same job, or even if techie just wanted to know in what ways Excel supports regex. Unfortunately, both Find and Regex are terms that can have different contexts.

We will just have to wait and see if techie ever comes back as it's a moot point until then...

ξ

EDIT:
Though, Leith - I'm not familiar with this syntax in RegExp.Replace()
ModifyTime = re.Replace(DateExp, "$1 $2")
I'll look that up in the RegExp reference tonight unless you have time to explain it first ...
(I assume we are somehow dealing with the parenthetical groups in the regex pattern).

I was trying to replace thousands of date time values in one shot in Excel if it supports regular expressions as it supports in Word. Looks like there is no direct replacement otherthan using some functions. But anyways thanks for all your prompt inputs.

 

[techiewithinme]

Hello techiewithinme,

Following up on GTO's sugeestion of using a UDF. Here is an example with a Regular Expression.

Function ModifyTime(ByRef DateExp As Variant)

    Dim RegExp As Object
    
        Set RegExp = CreateObject("VBScript.RegExp")
        RegExp.Pattern = "(.+)\:\d{3,}\s(.+)"
        
        ModifyTime = RegExp.Replace(DateExp, "$1 $2")
        
End Function

How to use this function in excel?

 

[Rick Rothstein]

Function ModifyTime(ByRef DateExp As Variant)

    Dim RegExp As Object
    
        Set RegExp = CreateObject("VBScript.RegExp")
        RegExp.Pattern = "(.+)\:\d{3,}\s(.+)"
        
        ModifyTime = RegExp.Replace(DateExp, "$1 $2")
        
End Function

How to use this function in excel?

Once you install the code in a standard module (the same place you put macro code), then you just use the function like any other built-in Excel function. For example, if you had a text string like the OP posted in A1, then you would simply put this formula in the cell you wanted to display the result in...

=ModifyTime(A1)