Excel - Extracting Website Data into Excel: Mastering Data, Text to Columns - Episode 566
1,047 views · Published 6 July 2009 · 3:28 · Indexed 18 October 2026
Channel: MrExcel.com · 2009 · Education
Microsoft Excel Tutorial - Extracting Website Data into Excel: Mastering Data, Text to Columns. Welcome back to the MrExcel netcast! In today's video, we're going to tackle a question sent in by Dave about how to extract data from a website and get it into Excel. Dave was watching an old podcast where I showed how to use a Pivot table, but he had a simpler question - how did I get the data from the website in the first place? Well, the answer is Data, Text to Columns. In the podcast, I briefly mentioned using Data, Text to Columns to separate the data into columns. However, it wasn't as simple as just one click. I had to use this feature multiple times and also do an edit replace to get the data clean. The first step was to use Data, Text to Columns and select "Delimited" as the data type. Then, I specified that the data was delimited by a semicolon. This gave me three columns - one for the date, one for the numbers, and one for the Mega Ball. Next, I needed to separate the Money Ball column. So, I used Data, Text to Columns again and this time selected "Delimited" and specified that the data was separated by a colon. Since there was no colon in the data, I had to manually add it in the next step. Then, I selected "Do not import" for the text that said "Money Ball" and clicked finish. This gave me a separate column for the Money Ball. But, there was still one more step to get the numbers in their own columns. I noticed that some of the numbers in column B had a colon in front of them, while others didn't. So, I used Edit, Replace to get rid of all the colons. Then, I used Data, Text to Columns one more time and specified that the data was delimited by a comma. This gave me all the numbers in their own columns, making it easy to pivot the data. In the podcast, I also mentioned that I had the date in column A and the numbers in column B. This was achieved by brute force copying and pasting the data into a new data set. So, sometimes with Data, Text to Columns, you may need to break down the problem into smaller steps. In this case, it took three text to columns and an edit replace to get the data clean. Thanks for watching and be sure to check out our other netcasts for more Excel tips and tricks! Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ Table of Contents: (00:00) Introduction (00:22) Getting data from Mega Millions website (00:32) Using Data, Text to Columns (01:05) Separating date and numbers (01:28) Separating Money Ball column (01:52) Cleaning up column B (02:14) Separating numbers (02:37) Arranging data for pivot table (02:56) Tips for using Data, Text to Columns (03:08) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial This video answers these common search terms: brute force copying data extraction Mega Ball Money Ball new data set Pivot table pivot. Replace semicolon Text to Columns website data Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152505/ Going back to Podcast 453, I talked about taking data from the Mega-Millions website and parsing this data in Excel. Dave wrote in to say that the text to columns process was not working out well. In Episode 566, I show how using Text to Columns sometimes requires a few extra steps. This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:37
Excel - Enhance Your Excel Charts: Add a Picture as a Background in Excel - Episode 917
-
2:00
Excel Hiding Data in Plain Site - Episode 919