Presuming you tried setting the column to text in Excel, you could also try moving one of the rows with special case cells (i.e. one with text as well) up to the top, or just adding text to that first row. JET (MS product that Tableau uses to bring in data from Excel and others) is reading the first x lines of your file and deciding it's a number. That's why all of the text cells end up as null.
If you can't do that for whatever reason, you may have to push the data to a text file and use a schema.ini: http://msdn.microsoft.com/en-us/library/ms709353%28VS.85%29.aspx
This is a bad situation as many users won't realize they have data issues; there's an idea here to get Tableau to implement better error checking: http://community.tableau.com/ideas/1104
Once you get the data in, you could use find(), left(), right() to parse it out.
Another option is to do this in Excel. Either do text to columns on a space, or replace % with something like %~ and text to columns on ~