Parsing text to data columns

English
, ,
#1

How can I parse text in column A such as “Wednesday, 11/15/2023 - 3:00am - 4:00am” into two new columns formatted for date and time, such as: “11/15/2023” and “03:00” ?

Each month I do a lengthy analysis of electric use and solar generation kwh, costs, and credits. Date and time are important as the billing rate changes by day of the week, holidays, season, and time of day. The solar company downloads the data labeled with this very unhelpful text field showing date and time. It’s easy to manually parse one line into useful data fields but each day has 24hrs and each month has about 30 dates and 720hrs making this a very time consuming chore. Surely there is a better way. Help would be appreciated. (Sample download attached)
sunpower_export_20231115_20231115.xlsx (17.7 KB)

#2

The easiest way is to:

  1. insert 5 columns before B
  2. Select the data in column A and click Data > Text to columns
  3. in the dialogue that opens
    1. under Separator Options, tick Space
    2. make sure Detect special numbers is ticked
    3. select the header of the second column (of dates) and then under column type select Date (M/D/Y)
  4. OK
  5. Delete unnecessary columns
1 Like
#3

Wow!!! I am impressed. So much time could have been saved if I had known about this. I was sure it would require a complicated macro.

One note: It worked great the second try. On the first try the time came out as ###. To get time properly it was necessary to set the time column to “US English” and then format it to time.

Much thanks.

#4

‘###’ means the contents are too wide for the cell, just widen the column

#5

You are correct and I am a dummy. I thought I had tried that but was so excited about the solution that I overlooked the obvious.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.