One of the most frequent questions we get is: The players names and team names are garbled with HTML when I export data. How can I fix this?
Simple Tag Removal:
To remove any HTML tags from the data all you need to do is a quick Find & Replace All.
Fill in “<*>” (no quotes) into the Find what box, and then leave the Replace with box blank and hit Replace All.
And that’s it. You now have a worksheet completely free of all HTML.
You Aren't a FanGraphs Member
It looks like you aren't yet a FanGraphs Member (or aren't logged in). We aren't mad, just disappointed.
We get it. You want to read this article. But before we let you get back to it, we'd like to point out a few of the good reasons why you should become a Member.
1. Ad Free viewing! We won't bug you with this ad, or any other.
2. Unlimited articles! Non-Members only get to read 10 free articles a month. Members never get cut off.
3. Dark mode and Classic mode!
4. Custom player page dashboards! Choose the player cards you want, in the order you want them.
5. One-click data exports! Export our projections and leaderboards for your personal projects.
6. Remove the photos on the home page! (Honestly, this doesn't sound so great to us, but some people wanted it, and we like to give our Members what they want.)
7. Even more Steamer projections! We have handedness, percentile, and context neutral projections available for Members only.
8. Get FanGraphs Walk-Off, a customized year end review! Find out exactly how you used FanGraphs this year, and how that compares to other Members. Don't be a victim of FOMO.
9. A weekly mailbag column, exclusively for Members.
10. Help support FanGraphs and our entire staff! Our Members provide us with critical resources to improve the site and deliver new features!
We hope you'll consider a Membership today, for yourself or as a gift! And we realize this has been an awfully long sales pitch, so we've also removed all the other ads in this article. We didn't want to overdo it.
More Advanced Parsing:
But since we’re leaving the HTML in, there are some neat tricks you can do if you want to preserve the playerid.
Step 1: Highlight the entire A column and do these replace all’s in sequence, replacing them with nothing (no quotes):
“<a href=”statss.aspx?playerid=”
“position*>”
“<*>”
Step 2: Add an additional column next to first column.
Step 3: Finally you’ll need to run the Text to Columns wizard, which you can access by holding alt + d + e in that order or clicking on it under the data menu. Select the delimiter Other, use the & symbol, hit finish and you’ll have a properly formatted worksheet with playerids preserved.
Using similar techniques, you can preserve teamids and player positions. If you find yourself doing more involved parsing frequently, it’s also possible to record the steps taken as a macro for a one click solution.
Great post, David. Thank you.
Thanks for the suggestion on parsing out the player ID number. Helps to keep same-named players separate, and makes it easier to work with if you’re trying to match up the players in two overlapping sets of data.
Random thing that would be helpful with fangraphs’ data: an “active player” checkbox.
We’re looking into doing something with “Active” players. Or at least players on the 40 man roster in the off-season / 25 man when those kick in.
With the custom leaderboards, hopefully there is not too much of a reason to ever try and match up multiple data sets in Excel, unless you’re trying to do something with splits, or across batters / pitchers/ fielders.
The custom leaderboards are great. You still need to match up different data sets if you want to look at two different seasons (pitchers who pitched X number of innings in both 2010 and 2011), or if you’re using splits data (home/away or left/right).
I just do a text to columns with the “” symbols. Then I just delete the columns that have the gobbledygook. It’s an extra step, but having access to the custom leaderboard is worth the extra five seconds.
Off Topic.
Can you add a Thumb Up and Thumb Down option for articles (like exists for comments)?
I’d been using LEFT(), RIGHT(), and MID() (in conjunction with FIND() and LEN()) to accomplish the same thing. This definitely might be easier for your guys data, but I think you’re going to need to be familiar with those tools when you run into Lastname, Firstname lists.
I’m sure there is a very valid reason that I am just missing, but why insist on exporting data with htmls in the first place? It can’t be that hard to just have it export the playerid, teamid, and position, can it?
I agree. Or at least have the same question. Why? Why do your readers need to jump through so many hoops. Take the HTML out. I have a couple of C and VB utilities that I wrote to do this.
What is the license on exported data? I’m a little surprised your data providers let you do this.
All the data on FanGraphs, exported or not, is for private, non-commercial use, or may be used in accordance with fair use.
Thanks for the response.
Off topic but the splits pages for players are much more detailed than the league stats pages. Is there a way to either export data directly from the individual splits page for a set of players or since they are already being uploaded into the site change it so the data also loads into the league stats pages.
Specifically I want pitcher 2B and 3B allowed for a wOBa expirement.
Thanks for this too Dave, I’ve wondered how to counter this.
I have a macro and it makes it so easy. Just record a macro. I did position too.