2 Replies Latest reply on Nov 29, 2016 8:40 AM by Vincent Baumel

    How do I fix Excel auto-formatting in my data prep?

    Vincent Baumel

      I'm putting together a data source of NBA player stats, and when I paste in a table from the web it auto formats the field. I don't know how to fix this in Excel, and I'm wracking my brain trying to figure out how to correct it in Tableau. Any ideas?

       

       

        • 1. Re: How do I fix Excel auto-formatting in my data prep?
          David Li

          Hi Vincent! If you want to fix this in Excel, what you should do is paste the data in, then select the "Ht" column and change its number format to "Text". (You can do this in the Home page of the ribbon.) Then, paste again in the exact same spot. That should tell Excel not to convert those numbers to dates.

           

          Another thing you can do if you don't want to try to fix it in Excel is to convert that field back to heights. Just make a calculated field like this:

          STR(MONTH([Ht])) + "-" + STR(DAY([Ht]))

          1 of 1 people found this helpful
          • 2. Re: How do I fix Excel auto-formatting in my data prep?
            Vincent Baumel

            This is getting me really close, but a problem comes up when I convert the [Ht] field to a date - specifically the players whose heights end in 0. I modified the calc to take that into consideration, which looks like this:

             

            IF LEFT(STR([Ht]),4) != "2000" THEN STR(MONTH([Ht])) + "-" + STR(DAY([Ht]))

              ELSE STR(MONTH([Ht])) + "-0"

            END

             

             

            This seems to get me the last step towards where I want to be.  Thanks for the help, David!