You’re bound to run into an excel sheet where differing data types are joined together in a single column. For example in the following data dates are combined with event names making it impossible to filter or sort by either date or event. By using Excel’s “Text to Columns” feature, we can separate these two distinct data points.
As long as you have a consistent delimiter (in my case, the pipe character “|”) or a pre-determined fixed width, you can easily move the text following the delimiting character (or exceeding the specified width) to the adjacent column (which I’ve inserted in advance to prevent overwriting existing data in the original second column).
- Select column or cells you’re splitting
- Select the “Data tab” then “Text to Columns”
- Choose to split by “delimiter.” Click next.
- If your delimiter isn’t an option, enter it as I’ve done (max 1 character) in the “Other” space. Click next.
- You can choose to change data formats here, so I’ve made my new first column date format for example. You can always change column data types later as well.
- Finish
Now you can sort and filter correctly on the values you separated.
