How to extract the text within the brackets from a string?

[hclifford]

Hi all,

I have a column containing records of the timestamp of an event. I need to extract the date out of each of these records and put them in a separate table. The date and time of each record is contained within a bracket.

E.g.
ES~1~1412179200(Oct 02 00:00:00 2014)~1~ITM_W64MQMONITORLOG00~
0~0~ES~1~1412179203(Oct 02 00:00:03 2014)~1~ITM_Log_Entries~
0~0~ES~1~1412179204(Oct 02 00:00:04 2014)~1~ITM_Log_Entries~

As you can see, the number of characters of each record are different, so I am unable to do the Left$ count thingy.

Is there any other way to do it? I only need the date and time contained in the brackets.

Thanks in advance!

 

[TJPoorman]

Try something like this:

Dim strTemp As String
Dim myDate As String

strTemp = Mid([myTimeStamp], InStr([myTimeStamp], "(") + 1)
myDate = Left(strTemp, InStr(strTemp, ")") - 1)

Debug.Print myDate

Of course, you'll need to figure how to format this as a date if you need it in Date/Time format.

 

[hclifford]

Try something like this:

Dim strTemp As String
Dim myDate As String

strTemp = Mid([myTimeStamp], InStr([myTimeStamp], "(") + 1)
myDate = Left(strTemp, InStr(strTemp, ")") - 1)

Debug.Print myDate

Of course, you'll need to figure how to format this as a date if you need it in Date/Time format.

Thanks! This helped a lot!