Converting Text String to Date Format

MaxxLevell

New Member
Joined
Nov 19, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
I have a csv that is showing the following text in a date column... October 30 2020 10:22 am How can I convert this to a date format? I have tried, unsuccessfully, using the Value() function, but it still does not recognize it. Any help would be appreciated. I feel like I'm missing something simple.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

JGordon11

Well-known Member
Joined
Jan 18, 2021
Messages
555
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Add a comma after the day (in this case October 30, 2020 10:22 am) and it should convert automatically
 

MaxxLevell

New Member
Joined
Nov 19, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Add a comma after the day (in this case October 30, 2020 10:22 am) and it should convert automatically
Thank you...that worked on the field I tried it on. Now my issue is how to add a comma after the second "word" in each string. I've found lots of info on how to substitute a comma after the first word, or after every word...but, I can't seem to figure out how to just add one comma after the second word. I have nearly 30,000 records I need to modify so they show properly.
 

JGordon11

Well-known Member
Joined
Jan 18, 2021
Messages
555
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
If you can use VBA then select the range to work on (code assumes a single column of data) and run the following macro (on a test copy of your sheet):

VBA Code:
Sub AddComma()
    Dim t, r, i As Long, ubr As Long
    r = Selection.Value
    ubr = UBound(r)
    For i = 1 To ubr
        t = Split(r(i, 1), " ")
        t(1) = t(1) & ","
        r(i, 1) = Join(t, " ")
    Next
    Selection = r
End Sub
 
Solution

Sufiyan97

Well-known Member
Joined
Apr 12, 2019
Messages
829
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Try

and format cells as - mm/dd/yyyy h:mm

Excel Formula:
=SUBSTITUTE(A1," ",", ",2)+0
 

MaxxLevell

New Member
Joined
Nov 19, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
If you can use VBA then select the range to work on (code assumes a single column of data) and run the following macro (on a test copy of your sheet):

VBA Code:
Sub AddComma()
    Dim t, r, i As Long, ubr As Long
    r = Selection.Value
    ubr = UBound(r)
    For i = 1 To ubr
        t = Split(r(i, 1), " ")
        t(1) = t(1) & ","
        r(i, 1) = Join(t, " ")
    Next
    Selection = r
End Sub
Thank you for your assistance on this...it worked perfectly.
 

Forum statistics

Threads
1,176,119
Messages
5,901,483
Members
434,896
Latest member
Derquila

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
Top