There are many times when you have data in an Excel column where you wish you had a way to split that data into two separate columns, such as first and last names and email addresses vs. usernames. The Delimit function allows you to do just that!
I often run across situations where there is data in a column of an excel spreadsheet that I need to be able to split into two different columns of data so that I am able to better utilize the data that is available. A simple example of this is being able to separate first and last names into two columns so that I can sort by last name. Another example is to separate usernames from email addresses. The delimit function in Excel lets me do just that!
Follow these simple steps to utilize this functionality:
- Select the column that the data you wish to split is in.
- Select the Data tab from the menu.
- Select Text to Columns.
- Select the Delimited option
- Select Next.
A new window will pop-up:
- Choose what your delimiter is…in my example I am separating the username from the rest of the email address, so my delimiter is the “@” character. I select Other and enter that character. If you are doing first/last names, your delimiter will likely be a space.
- Preview the data to ensure it is doing what you want it to.
- Select Next.
You can now choose what format you want the new columns to take on:
- Select a column.
- Choose the data format you want the column to take on. Repeat steps 1 and 2 for each column.
- Select Finish.
Your data is now separated into two columns and ready to be manipulated.
Contributor: Paul Wagner
Paul is a Digital Learning Specialist for a school district near Houston, TX. He oversees the Learning Management System for his district.
Follow Paul on Twitter: @pwagnerlcisd