Messy data from a CSV import? We’ve all been there. Not to worry! Excel’s OG Text to Columns tool can easily clean this up for us.
Check out the gif above - we’ve got chaotic data from our CSV file that we need to separate into columns for analysis. First, we’ll select all of our data, and then we’ll open the Text to Columns tool by pressing ALT A E, or by navigating to the data tab and selecting “Text to Columns” under the Data Tools group. Now we’ll select “Delimited” as our file type because we’re splitting the text at a specified character that marks the end of one data point and the beginning of the next, which in this case will be a comma.
Because we want to split our data at each comma, we’ll want to uncheck all of the values under “Delimited” except for the comma, and then hit next. Finally, we’ll tell Excel where we want our first new column to start in the destination box. In this case, since we want the data in columns directly next to the original data, we’ll enter cell C3, hit finish, and say goodbye to our messy CSV data!
