Macro to insert specific date based on value from another cell-CodePudding

I have a cell with a string of numbers and letters. For example: 06052022_GST_402. In this example, the "06052022" part is a specific date (June 5, 2022). I want the macro to recognize the date from that value and enter the date in format 6/5/2022 into another cell.

CodePudding user response：

Here's a way you can pull this off:

'Split the string by underscore delimiter into array and grab the first element (this will be string '06052022')
string_date = Split("06052022_GST_402", "_")(0)

'From that string grab each date component and store in a variable
string_year = Right(string_date, 4)
string_month = Mid(string_date, 3, 2)
string_day = Left(string_date, 2)

'Using function `DateValue()` concatenate the string date parts into a date of format `MM/DD/YYYY` which DateValue will turn into an actual date. Store that actual date in variable date_date and do with it as you wish. 
date_date = DateValue(string_month & "/" & string_day & "/" & string_year)

'Stick that date in a cell
Worksheets("Sheet1").Range("A1").value = date_date

You could do this one in one ugly line too:

x="06052022_GST_402"
Worksheets("Sheet1").Range("A1").value = Mid(x, 3, 2) & "/" & Left(x, 2) & "/" & Mid(x, 5, 4)

But that's going to spit out a string representation of the date, not an actual date type and so it may act goofy in your sheet with any downstream functions that are expecting a date.