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.

 

[Matt Rogers]

Hi.

Is it correct?

Excel Workbook
	A	B
1	10/4/2011 01:10:11:000300000 AM	10/4/2011 01:10:11 AM
Sheet

 

[xenou]

Hi,

Another option is to format the cell to display the date in m/d/y hh:nn:ss AM/PM format. This would have the advantage (if it helps) of leaving the date as a true date rather than converting it to a text value - actually cell formatting doesn't change the underlying value at all but just how it looks to the user.

I can't seem to find a link with good screenshots right now except this dummies article:
http://www.dummies.com/how-to/content/formatting-dates-in-excel-2007.html

Edit: note, this applies only if the date you start out with is itself a true date I think.

 

[xenou]

@Screwdriver:
I think your formula is busted when the input is
10/14/2011 01:10:11:000300000 AM

Result looks like this:
10/14/2011 01:10:1 AM

 

[techiewithinme]

Hi.

Is it correct?

Excel Workbook
	A	B
1	10/4/2011 01:10:11:000300000 AM	10/4/2011 01:10:11 AM
Sheet

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

---------------------
[link removed]

 

[xenou]

Excel doesn't support regular expressions in its find/search dialogues (if I'm not mistaken). No go on that score I think.

 

[techiewithinme]

I found another easy formula

=REPLACE(A1,19,10,"") which will give

10/4/2011 01:10:11 AM

as the answer. But I want with regular expression. Is there any good source info using regular expression in excel?



----------------
[link removed]

 

[xenou]

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.

 

[GTO]

Hi Techie,

I am probably misunderstanding, but just in case... Are you looking to use REgExp in a UDF to return the desired value?

Mark

 

[Leith Ross]

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