Insert comma to change data format

I'm very new to macros but I have a problem that's probably pretty easy for some of you on here. I have several spreadsheets with approximately 100 rows and columns each. Some of the columns are supposed to be formatted as dates but they are entered as text (ex, Jan 05 2023 8:24 AM). I can't sort as a date in this format and changing the format to "Date" doesn't work. I found that when I add a comma between the day and year (05 and 2023 in my example) the cell converts to date format.

I think I need a macro to look for all cells in this format (3 text characters then a blank and 2 numbers) and insert a comma into the 7th position.

Can somebody help me out or offer a simpler approach?

Thanks in advance.

Sample data.xlsx

Welcome to the forum.

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

I used a formula like =VALUE(REPLACE(B1,7,1,", ")) and that converted all of the text dates in your example to date numbers. I think that should work as long as ti replacement always needs to occur at the 7th position in the text string, and the rest of the date string matches what your system expects for date+time text.

Personally, I’d use a formula and copy and paste the values.

Formula:

Please Login or Register  to view this content.

Format as date/time.

And, if I was using VBA, I'd just drop that formula in a cell and convert the results to text

Oddly, when I moved the file over to my laptop, the formula that I suggested would not work. Nor would MrShorty's solution. Very strange, as it seemed happy enough on my iPad. Anyway, a little rework and we have these options:

Single cell, copied down:

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

SPILL formula (no need to copy):

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

And, VBA to implement the last formula:

Please Login or Register  to view this content.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon (Next to Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Perhaps...Unless I am missing something...

Please Login or Register  to view this content.

You're welcome.



Also, you may not be aware that you can thank those who have helped you by clicking the small star icon (Next to Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.