If you want an example of what's been done with it in the finished product, look no further than the "Viking" arcade cabinet in the Cantina. A couple of custom maps like the tower defense one have somewhat utilized the extreme promise with the StarCraft II editors, but I'd imagine it will take a little longer to get anything radical. People are still experimenting with the tools, so I'd give it a couple of months before any significantly different content is released.

Upon re-reading my post I realize I may have phrased my question awkwardly, but what I was really asking wasn't "Where can I find custom content on the internet", but rather "How can I actually open that content in the game". I've since figured that out, but I must say, I'm less than impressed with how Blizzard have implemented it. There really isn't any way to load a custom map from within the game. You have to drag a map file to your SC2 shortcut, which will then launch the map. And that's only for singleplayer maps. If you want to play multiplayer custom maps (and I haven't tried this, but this is the description I've read on various forums), the list of available multiplayer games is sorted by the popularity of the map in question, and you cannot sort it otherwise. So if you don't want to play one of the most popular maps, you have to keep clicking "next page" until you find a map you like.

Basically I think Blizzard didn't have time to quite finish the in-game support for custom maps properly. Judging by the amount of flak they're taking for this, it'll be improved in a patch at some point.

Actually, you can set the custom maps to be sorted by newest instead of popularity.

Let me just make it perfectly clear that I love banshees.

Fixed that for ya. :)

Hehe, banshees aren't that effective against toss though. I mainly use them in TvZ and TvT. Although the mass-stalker-but-no-observer combo has probably caused a few broken keyboards when I come calling with my 3 banshees.
Seems most players in the silver / gold leagues even are using cookie-cutter strats. I have barely faced a toss player who doesn't turtle and mass stalker. A very bad way to play toss vs. terran. Any early stalker push is easily countered by marauders, blink or not, and their 4 gateways is just sitting pretty much useless while I go straight for 3 starports and BCs. BCs eat protoss armies.

BCs? :|
To be honest, I've never ever seen a BC, Carrier or {whatever_the_zerg_have} in any of the matches I've played. But the quality of the matches has dropped a lot since the beta so I won't be surprised if I see such a unit once I get the game.

Free character name change / Creation of additional characters for a fee coming soon!
Clicky

Doesn't bother me too much for SC2, but are they gonna do this for Diablo 3?

They're gonna do it for every game and every feature as long as it ends up costing you $$$.

Ordered the game from Sendit, yesterday. Waiting for it to arrive.... Come ooooooooonnnnnnnnnnnnn!

SC2 offline mode is dead. Blizzard, what the fuck?

By dead I mean I cannot play as it requires activation. Okay, because I had a trial account. Maybe they fixed that. But a friend of mine has a proper account, logs in daily and still, the offline mode doesn't work for him either.