Split & Convert
By Richard Rost
9 months ago
Split Date Ranges and Convert to Actual Dates
In this Excel tutorial I will show you how to import messy CSV data into Excel, use the Text to Columns feature to split it into separate fields, format the dates correctly, sort the data, and create a simple line chart to visualize trends. This lesson is great for beginners and focuses on practical, step-by-step techniques with no advanced formulas or programming.
Prerequisites
Links
Recommended Courses
Learn More!
Questions?
Please feel free to post your questions or comments below. Thanks.
Keywords
TechHelp Excel, Text to Columns, split CSV data, date formatting, month-day-year conversion, line chart creation, importing data to Excel, sorting data, ISO date format, CSV to Excel columns, water usage chart, Excel beginner tutorial, resizing columns, Excel date values, Excel TechHelp, deleting columns in Excel, Excel data cleaning, chart outliers, Excel data import, Excel line charts
Intro In this video, we will learn how to take a messy CSV file, import it into Microsoft Excel, and use the Text to Columns feature to split the data into clear columns for start date, end date, and gallons used. I will show you how to set up the correct date formats, sort your data, adjust columns, and create a simple line chart to help visualize your usage. This tutorial is aimed at beginners, with step-by-step instructions covering only the essentials for cleaning and organizing your data.Transcript Welcome to another TechHelp video brought to you by excel-learning-zone.com. I'm your instructor, Richard Rost.
Today, I'm going to walk you through taking some data that I got that looks like this - a big mess. We're going to split it up into individual fields and then we're going to make sure that these convert over to proper dates so we can make a nice, pretty chart that looks like that.
Normally, these are Q&A type videos where I get a question from a user, but today the user is me. I got my regular water and sewer bill from the city of Cape Coral, and they were saying that I used 19,000 gallons of water last month. Usually, my bill is for four or five thousand, so something is up, but that's beside the point.
I wanted to download my usage from their website, which they offer, so I can look at the past several months and compare. So, I did, and they give you a CSV file - a comma separated value file - and it looks like this. It's a mess, and it's not even sorted. Look at this: 24, 25... It's not even sorted by the first.
What I want to do is show you how to take this data, bring it over into Excel, and split it up so we have three nice columns: the start date, the end date, and the gallons. Then we can make some sense of it, sort it, and plot it as a proper line graph.
This one's for the beginners. There aren't a lot of fancy formulas, functions, or programming in this. I'm going to show you how to walk through this.
If you haven't watched my Excel Beginner Level 1 class, go watch this. It's about an hour long, absolutely free, and teaches you all the basics.
To make sense of this, I'm going to copy all this data. Just Control+A, Control+C will copy it to your clipboard. We're going into Excel and just pasting it in. When you paste it in, it comes into one column like that. See? That's all one column.
We're going to use Text to Columns to split this up. Go to Data and then Text to Columns to bring up the wizard. Is it delimited or fixed width? It's delimited. Next.
Delimited means there is a character that separates the columns. Comma for sure, and notice it separated that guy out down there. Also, we can use that dash to separate the start date from the end date. Go to Other, and then in here put a dash. Look at that - it separates those out as well.
Now we have three distinct columns. Next. Here is where you can set the data.
Their format is Month-Day-Year, which is typical for the United States. Make sure you pick Date, and then here you're going to go Month-Day-Year. Same thing over here with this guy: Date, Month-Day-Year.
This one here is just a number, so General is fine. This really isn't anything over here, but there is a comma, so it's going to treat it like there's another column over here. Don't worry about that.
Hit Next. Finish is grayed out, so hit Finish. There we go. Now, we need to do some adjusting in here.
I'm going to put in here - instead of "billion period," this is the Start. This is the End, and this is the Gallons over here.
Select all three of these and double-click on one of those to resize those columns. Maybe make them a little bit bigger.
There we go. I personally like everything left-aligned. That's just me. That's my personal preference.
Now that these are actual dates, we can properly sort the data. I'm going to zoom out just a touch so we can see it all. Yep, there it is. Zoom back.
I like it like that. I am going to select all of this data and hit the A to Z, and that'll sort it. Excel is now smart enough to know that those are headers.
In older versions, you used to have to not sort those columns in. Now you can see everything is sorted properly. I can zoom back in now.
Here you can clearly see there is the anomaly right there. Yes, it was a little bit high last year around the same time, but nowhere near as it is now. This is crazy.
If you want to see how crazy it is, just again, select all of your data. Go to Insert, then insert a chart. I like line charts, so I'll pick the line chart. Let's go with this simple one here. There we go.
Now you can see right there. We don't really need all of that data in there. In fact, now that I've got it in here, I almost don't want that end date. We really don't need that end date; it's kind of superfluous. It's the same as the start, so we're just going to delete that end date.
That looks a lot better.
There you go. Now I can see - and the funny thing about this is this month, today is when I'm recording this - June 18th. This month is almost over. If this is accurate, that means I'm right back down to where I started from, and yes, I've checked for leaks and all that stuff. I just want to see what caused that spike last month.
It's pretty easy to do - just bring it in, use your Text to Columns, and make sure you have the right date format.
Notice how it switched it over to the ISO date standard for me. Once you identify that as a proper date value and Excel stores it internally as a date, which is a number, then you can display it in whatever format you want and Excel will show it in, by default, the Windows default date format. I have the ISO date format, which is year-month-day, and I am on a mission to get the entire world to use this as the date standard. Go watch this video for more information and to hear me complain about it.
If you want to learn more about that Text to Columns feature that I used a few minutes ago, go watch this video. It's got some great stuff. You'll find links to both of those videos down below.
Don't forget to check out my Excel Beginner 1 class.
Now that you've got the data into Excel, it's very easy if you want to import that into a Microsoft Access database. I'll be honest - most of what I teach is Microsoft Access, but even I will drop small bits of data into Excel to massage it and get it into the proper format, then I'll import it into Access. Unless we're talking about thousands of records, then I'll just import it raw into Access and we'll figure it out with queries there. If it's a tiny bit of data like this, Excel is perfect for that job.
Remember, use the right tool for the right job. Sometimes it's Excel. Sometimes it's Access. Sometimes you can just get along with Notepad.
I thought I was going to be dealing with Notepad - thought I'd just download it, and look at it, but then noticed it wasn't even sorted. I couldn't make heads or tails of this, so I figured I should show you how to do it too.
That's going to do it, folks. That is your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
I'm excited to announce that I'm creating a brand-new series of lessons focused on programming in Microsoft Excel VBA. If you've been looking to take your Excel skills to the next level and learn how to automate tasks, write custom macros, and unleash the full power of Excel VBA, these lessons are for you. Sign up now on my website at the link shown. You'll find a copy in the description down below the video window, and I'll send you more information on this exciting new series.
If you'd like to see me make more Excel TechHelp videos, post a comment down below and say, "I want more Excel!"
The vast majority of my videos are for Microsoft Access - the database program - because that's been my forte for the past three decades or so. However, I love Excel and I'm more than happy to make more videos for Excel, Word, PowerPoint, and all the other topics that I teach.
As you know, the squeaky wheel gets the grease, so make your voice heard and let me know you want more Excel videos.
TOPICS:
Importing a CSV file into Excel
Pasting CSV data into Excel
Using Text to Columns to split data
Selecting delimiters in Text to Columns
Splitting dates using comma and dash delimiters
Setting date formats in Text to Columns
Assigning column headers after splitting data
Adjusting column widths in Excel
Sorting data by date in Excel
Creating a line chart from data
Deleting unnecessary columns in Excel
Changing date display formats in Excel
COMMERCIAL:
In today's video, we're learning about how to take a messy CSV file from your water bill, bring it into Excel, and split it up into neat columns using the Text to Columns feature. I will show you how to handle the date formats, sort your data, and make a simple line chart to spot unusual spikes in usage. This tutorial is perfect for beginners—no fancy formulas, just the basics to clean up your data so it actually makes sense. You will also hear some tips about when to use Excel versus Access and get pointers on where to learn more. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What was the primary issue with the CSV file Richard imported from his city utility website?
A. It was full of duplicate entries
B. The data was all in one column and unsorted
C. The file was encrypted and unreadable
D. Each data value was in a separate file
Q2. What Excel feature did Richard use to separate the data into appropriate columns?
A. Find and Replace
B. Text to Columns
C. Conditional Formatting
D. Data Validation
Q3. When using Text to Columns, what type of delimiter did the CSV file use?
A. Semicolon
B. Tab
C. Comma
D. Pipe
Q4. Besides the comma, which character was used to further split the data within the columns?
A. Colon
B. Period
C. Dash
D. Slash
Q5. When setting up the Text to Columns feature, why is it important to specify the date format?
A. To ensure Excel correctly interprets and stores the date values
B. To automatically create a calendar
C. To apply conditional formatting
D. To protect the data from being changed
Q6. Once the data was split and formatted, what did Richard recommend doing to the columns for better readability?
A. Hide empty columns
B. Rename columns and resize them
C. Merge all columns into one
D. Format all cells as currency
Q7. What procedure did Richard use to sort the dataset by date?
A. Used the filter feature
B. Sorted by gallon use
C. Selected all data and clicked the A to Z sort button
D. Used PivotTable functionality
Q8. Why did Richard decide to delete the 'end date' column before creating the chart?
A. It contained errors
B. It was identical to the 'start date' and unnecessary for the chart
C. It was not formatted as a date
D. It was missing in most rows
Q9. What type of chart did Richard choose to visualize the water usage data?
A. Pie chart
B. Bar chart
C. Line chart
D. Scatter plot
Q10. Why is it important that Excel stores dates as actual date values?
A. To take up less memory
B. To allow sorting and formatting in any date format
C. To automatically spell-check date fields
D. To secure the file from being edited
Q11. According to Richard, which date format does he prefer and advocate for universal use?
A. Day-Month-Year
B. Month-Day-Year
C. Year-Month-Day (ISO format)
D. Year-Day-Month
Q12. When should Excel be used instead of Microsoft Access for data manipulation, according to the video?
A. Only when working with hundreds of thousands of records
B. For quickly massaging small sets of data before importing to Access
C. Excel should never be used for data manipulation
D. Only for calculating budgets
Q13. What advice does Richard give regarding the use of Excel, Access, and Notepad?
A. Only use Access for everything
B. Use Word for all text files
C. Use the right tool for the job depending on the data and task
D. Notepad should be avoided for editing any files
Q14. What beginner-level resource does Richard recommend to viewers who are new to Excel?
A. Read the Excel help files
B. Watch his Excel Beginner Level 1 class
C. Download an Excel template from Microsoft
D. Take a university course
Q15. What new series does Richard announce at the end of the video?
A. Lessons on Microsoft Access security
B. Programming in Microsoft Excel VBA
C. Advanced PowerPoint animation techniques
D. Writing SQL queries in Notepad
Answers: 1-B; 2-B; 3-C; 4-C; 5-A; 6-B; 7-C; 8-B; 9-C; 10-B; 11-C; 12-B; 13-C; 14-B; 15-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Excel Learning Zone will walk you through how to take a messy CSV file containing water usage data and turn it into a clean, organized Excel spreadsheet that you can use to create meaningful charts.
This time, the question actually comes from me. I recently received my water and sewer bill from the city and noticed it listed an unusually high water usage last month. Usually my bill reflects four or five thousand gallons, but this time it said nineteen thousand, so I wanted to review my historical data to get a better understanding of my usage patterns. The city provides a download of your usage data in CSV format, which stands for comma separated values. When I opened the file, it was not only disorganized but also unsorted. For example, the days were not in order, making it very difficult to analyze at a glance.
The goal here is simple: bring this data into Excel, separate it into clear columns for start date, end date, and gallons, and then sort and plot it so it becomes useful.
This lesson is perfect for beginners. You do not need to know any complex formulas, functions, or programming. I will guide you step by step.
If you still need a foundation in Excel basics, I recommend my Excel Beginner Level 1 class. It is a free, hour-long course that covers everything you need to start working confidently in Excel.
To begin, select all of the data in your CSV file and copy it. Paste this data into a blank Excel worksheet. Initially, everything will appear in just one column. That is expected.
Next, you will want to use the Text to Columns feature. This will help break the combined data into separate columns. Go to the Data tab in Excel and find the Text to Columns tool, which launches a simple wizard to guide you.
You will tell Excel that the data is delimited, meaning a specific character separates each field. In your CSV, it is clearly separated by commas, and within one of the columns, the start and end dates are separated by a dash. Indicate to Excel that both comma and dash are delimiters, and you will see the data previewed as three columns.
Continue through the wizard. Excel gives you a chance to set the data type for each column. The dates from the CSV are in the Month-Day-Year format, typical for the United States. Make sure you select Date and set the appropriate format for both the start and end date columns. The gallons used is just a number, so leave that set as General.
You might notice an extra column appears with empty values. This happens if your CSV includes trailing commas, but you can ignore or delete this extra column after the split.
Once that is done, you can rename your columns to something clear, like Start, End, and Gallons. Make the columns wider if you want by double-clicking their borders, and feel free to adjust the alignment to your liking. I prefer left-aligned myself.
Now that Excel recognizes the dates correctly, you can sort the records based on the date values. Just select your range and use the Sort feature. Modern versions of Excel automatically detect headers, so your labels stay put.
Once sorted, the usage anomalies stand out visually. For example, my much higher usage from last month is very clear.
With the cleaned and sorted data, making a chart is simple. Highlight your data and choose a chart from the Insert tab. I prefer a simple line chart for this kind of historical trend, and it works perfectly here to show how my usage changed over time.
After plotting, I realized that having both start and end date columns was unnecessary for my purposes, since the end date offered no additional helpful information. I just deleted that column to keep things tidy.
Working with proper date formats in Excel is important. Once Excel sees these as real date values, it handles sorting and formatting smoothly. For consistency, I like to use the ISO date format (year-month-day). This avoids any confusion that might come from regional differences in date styles. Once Excel has the date information stored as dates, you can display them however you like, and your system's regional settings will take care of the display.
If you want more information about the Text to Columns feature or are interested in why the ISO date format is so useful, you can find additional instructional videos on my website linked below.
While most of my teaching is focused on Microsoft Access, I find Excel is sometimes the best tool for small-scale data cleanup like this. You can easily massage or reformat your data in Excel and then, if needed, import it into an Access database for more complex analysis. Remember to use the right tool for your needs. Sometimes that is Excel, sometimes Access, and occasionally just a quick look in Notepad.
I initially tried to review this data in Notepad, but since the file was unsorted and difficult to decipher, I decided Excel was the better option and wanted to show you how to accomplish this as well.
If you'd like to build your skills further, I'm working on a new series about programming in Excel VBA. These lessons will show you how to automate tasks, create macros, and unlock more of Excel's advanced capabilities. If you're interested, you can sign up for updates and more info at my website.
Most of my content covers Microsoft Access because that has been my specialty for decades, but I am happy to make more videos about Excel, Word, PowerPoint, and beyond. If you want to see more Excel lessons, let me know by commenting that you want more Excel tutorials. The more requests I get, the more likely I am to produce more Excel content.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Importing a CSV file into Excel
Pasting CSV data into Excel
Using Text to Columns to split data
Selecting delimiters in Text to Columns
Splitting dates using comma and dash delimiters
Setting date formats in Text to Columns
Assigning column headers after splitting data
Adjusting column widths in Excel
Sorting data by date in Excel
Creating a line chart from data
Deleting unnecessary columns in Excel
Changing date display formats in Excel |
