Trying (unsuccessfully) to pull a date from the middle of a string

[zookeepertx]

Hello all,

I have spent 2 days trying to get this to work right and I'm obviously leaving out a parenthesis or a comma or something.

So, I'm trying with VBA to extract just the date from the following text:

Date created: 9/24/2024 2:35:55 PM

I don't want the spaces before or after the date and whether the month and day are 1 or 2 characters, it needs to show the whole date. (I don't want a formula in the report, I just want the date to appear in F2 (or whatever row is the current destination row).
My thinking was to use MID. Establish the cell being evaluated, find the first character after the : - which would be 9 in this case - to use as the start character. Then - to find the length of the result I need - find the first space after the: and subtract it from the length of text in the cell.

Here's what I've arrived at at this point but it's not working:

Mid(Cells(i, 1), (InStr(Cells(i, 1), ":") +2), (Len(Cells(i, 1) ) -((InStr(Cells(i,1), " " ) , (InStr(Cells(i, 1), ":") +2)-1) )))

But I keep getting a "Compile error: Expected )" message.

Can someone enlighten me as to what I'm leaving out or have in there that I don't need?

I would be very grateful; I'm so tired of looking at this I could tear my hair out!

Jenny

 

[Joe4]

Do all the entries follow this exact format?

Date created: 9/24/2024 2:35:55 PM

If not, can you show us examples of other formats these entries might appear in?

 

[zookeepertx]

Do all the entries follow this exact format?

Date created: 9/24/2024 2:35:55 PM

If not, can you show us examples of other formats these entries might appear in?

This is a new report for me to work with but as far as I can tell, they're all the exact same format.

 

[Joe4]

Does this need to be VBA?
This can be done real easily with new functions available in Excel 365.
For an entry in cell A1, you can use this formula:

=TEXTBEFORE(TEXTAFTER(A1,": ")," ")

 

[Joe4]

Using VBA, here is one way you could do it:

Sub MySplit()

    Dim str As String
    Dim arr1() As String
    Dim arr2() As String
   
'   Get value to split
    str = Range("A1").Value
   
'   Chop off beginning of string
    arr1 = Split(str, ": ")
'   Chop off end of string
    arr2 = Split(arr1(1), " ")
   
'   Return date portion of string
    MsgBox arr2(0)
   
End Sub

I added documentation to explain what is going on each step of the way.

 

[Joe4]

If you are going to have to do this a number of times and want to use VBA, we can actually build our own User Defined Function (UDF) to do it, like this:

Function GetDate(str As String) As String

    Dim arr1() As String
    Dim arr2() As String
    
'   Copy off beginning of string
    arr1 = Split(str, ": ")
'   Chop off end of string
    arr2 = Split(arr1(1), " ")
    
'   Return date portion of string
    GetDate = arr2(0)
    
End Function

Then, you can call it from anywhere in your VBA procedures or as function on your Excel sheet, i.e.

Sub MyTest()
    MsgBox GetDate(Range("A1").Value)
End Sub

 

[Scott Huish]

=INT(TRIM(MID(A1,14,100)))

Function GetDate(r As String)
GetDate = Int(CDate(Trim(Mid(r, 14))))
End Function

Format result cell as Date

 

[zookeepertx]

I've already got part of a macro made to do some other stuff on the report. It's a list of files: each file is 31 rows long. and with the number of files involved the entire report is somewhere around 5,500 rows. But I only need the data for 9 rows out of each file. So it would be very time-consuming to do it with anything but a macro, at least I think so.
I've got all of the necessary rows returning the correct data except 2 of them. Both of those rows are in this same format.

(This report has been working perfectly for YEARS, but then they "upgraded" it and it's basically unusable in its current format, at least until I get this figured out.) It used to download in columns and each file only had 1 row. Now, not only is it in a single column, combining all the data for each file in a group of 31 rows, but I can't download it; my only choice is to manually highlight the 10 files on each page (because right-clicking doesn't give me any options like "Select all"), copy and paste into the spreadsheet. For 25 pages!

But I digress...
Jenny

 

[Joe4]

=INT(TRIM(MID(A1,14,100)))

Function GetDate(r As String)
GetDate = Int(CDate(Trim(Mid(r, 14))))
End Function

Format result cell as Date

Scott,

Your formula worked for me, but not the Function. Not sure why, but it seems to choke on the CDATE part of the function.

 

[Joe4]

I've already got part of a macro made to do some other stuff on the report. It's a list of files: each file is 31 rows long. and with the number of files involved the entire report is somewhere around 5,500 rows. But I only need the data for 9 rows out of each file. So it would be very time-consuming to do it with anything but a macro, at least I think so.
I've got all of the necessary rows returning the correct data except 2 of them. Both of those rows are in this same format.

(This report has been working perfectly for YEARS, but then they "upgraded" it and it's basically unusable in its current format, at least until I get this figured out.) It used to download in columns and each file only had 1 row. Now, not only is it in a single column, combining all the data for each file in a group of 31 rows, but I can't download it; my only choice is to manually highlight the 10 files on each page (because right-clicking doesn't give me any options like "Select all"), copy and paste into the spreadsheet. For 25 pages!

But I digress...
Jenny

I provided two VBA solutions. Do either of those work for you?