You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
(Who could be better than a Business Intelligence analyst at teaching us a thing or two about Excel? We asked our own number-crunching wizard Stacey Armstrong to share some Excel tricks she's learned along the way. Check out her first trick Resizing column widths in pivot tables. )
I have a list of URLs in one column and their corresponding page views in another. The URLs include a name for a section of the site and the date when the page was published. I'd like to calculate the total number of page views by site section and the total number of page views by publication date. To save time, I want to avoid manually entering this data in new columns. Instead, I plan on using the Convert Text to Columns wizard and Concatenate function. Once you learn the steps, it will take you only a few minutes to complete the tasks. These steps can also be applied to other situations when you need to split and merge data.
In the sample URLs below, I want to split off the site sections "contactus" and "home." And I want to split off the date that the pages were published "4/15/2012" and "4/11/2012". Since the URLs have slashes / between each of its parts, it will be easy use the Convert Text to Columns wizard (Wizard) to split off the section name and publication date, and turn them into new columns
Following these steps lets you split off data from the original URL and add it to the spreadsheet in such a way that lets me create a pivot table. You can use the pivot table calculate page view totals for site sections and dates.
More info on merging and splitting cells in Excel:
Merging and splitting cells or dataMerge and unmerge cellsVideo: Combine the contents of multiple cells into one cell Combine two or more columns by using a functionVideo: Use the Text to Columns WizardDistribute the contents of a cell into adjacent columns
Great post, Stacey, I always forget about the power of text to columns. Or maybe I just default to formula acrobatics.
I would suggest the use of =DATE(F2,D2,E2) in place of the concatenate function. This puts the data already in the correct format, and type.
I always enjoy these tips.
Yes, the DATE function will work as well. Thanks for pointing that out.
This example is an oversimplified version of what you get in real life. Normally, you don't get URLs as a cell contents, normally you get them as URLs inside the hyperlink. And in this latter case you're doomed: Excel can neither Find, nor Replace hyperlinks. Hopefully this is going to be fixed in Excel 2012.