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

zookeepertx

Well-known Member
Joined
May 27, 2011
Messages
586
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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?
 
Upvote 0
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.
 
Upvote 0
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:
Excel Formula:
=TEXTBEFORE(TEXTAFTER(A1,": ")," ")
 
Upvote 0
Using VBA, here is one way you could do it:
VBA Code:
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.
 
Upvote 0
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:
VBA Code:
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.
VBA Code:
Sub MyTest()
    MsgBox GetDate(Range("A1").Value)
End Sub
 
Upvote 0
=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
 
Upvote 0
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 :rolleyes: 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
 
Upvote 0
=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.
 
Upvote 0
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 :rolleyes: 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?
 
Upvote 0

Forum statistics

Threads
1,223,841
Messages
6,174,966
Members
452,593
Latest member
Jason5710

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top