Skip to main content

How to Split Excel Cells Using a Delimiter

Data imported from external sources might require some cleaning up. With the help of delimiters, you can split such data into different cells to organize it better and make it more meaningful.

A delimiter separates each part of data within that string. The delimiter can be any character like a comma, space, tab, or semicolon. However, splitting data into their cells in Excel is not that simple. So, let's look at how to manipulate data with the help of delimiters in a spreadsheet.

Select Your Data

Before you get started, you'll need to select all the data you want to use. Here's how to do this:

  1. Open a new Excel sheet. Enter sample data into the first column of the sheet.
  2. Type a list of names of a few singers and try to split these names into first names and last names into separate cells.
  3. Select the data you want to separate using your mouse or the Ctrl and Down arrow keys.
    Selecting data in MS Excel

Using the Text to Columns Option

Once the data is selected, you can split the names into first and last names. Let's see how!

  1. Click on the Data option from the Excel ribbon.
    Selecting data in MS Excel
  2. Click on the Text to Columns icon in the Data Tools section of the Ribbon.
  3. When you select Text to Columns, the Convert Text to Columns Wizard will open on the screen. The wizard will help you split the text in three steps.
  4. Select the Delimited option and press the Next button.
    Text to columns wizard in MS Excel
  5. On the next screen, select Space as the delimiter, and uncheck any other delimiters.
  6. Once done, press the Next button. The Data preview window will show how Excel will split the data.
    Data preview in Text to columns wizard
  7. You can choose the Destination where you wish to save the output data. MS Excel takes the first cell of the selected data as the Destination. However, for a filled sheet, you must remember to select the Destination cell. Otherwise, Excel will overwrite your original data. Do note that if your data changes, you'll need to do the text delimitation again.
  8. Adding destination for output data
  9. Once done, click on the Finish option. Excel will split the data into two columns with the first names and last names.
    Split data using Text to columns

A delimiter helps to set up the point where you can split any text. A "space" is just one type of a simple delimiter. For first, middle, and last names, the data can be split into three columns.

Similarly, other types of delimiters can help you split data that may not have spaces. Let's take a look at them in the next section.

Related: How to Combine Two Columns in Microsoft Excel (Quick and Easy Method)

Types of Delimiters

Other delimiters apart from the Space delimiter that you can use on data strings are the Comma, Semicolon, Tab, and Other. We already saw the Space delimiter when we learned to use the Text to Columns option. Here, we will see how to work with the Comma, Semicolon, and Other delimiters.

Comma

Let's say we have a data list of five friends and the country they live. We can split their names and their country into different columns using the comma delimiter.

  1. Select all the data and click on the Data option from the top of the screen.
  2. Click on the Text to Columns option from the Data Tools group on top of the Excel sheet. A wizard window will appear on the screen.
  3. Select the Delimited option and press the Next button.
    Selecting delimited option
  4. This time, choose Comma as the delimiter and click on the Next option given.
    Check box for Comma as delimiter
  5. The Data preview window shows you how Excel will split the data. You can choose the Destination where you wish to save the output data.
  6. Once done, click on the Finish button to separate the data into columns.
    Selecting General as Column data format

Semicolon

Here's a small dataset of students' registration numbers, along with their ages. You can split the student's registration numbers and their ages into different columns using the Semicolon delimiters.

From the second step of the wizard, select Semicolon as the delimiter.

As you can see on the screen below, the delimiter splits the student's registration number and ages into separate columns.

Other

Text to Columns option also allows you to choose your delimiter to split any text string through the Other delimiter. For instance, you can use it to split email addresses into usernames and domain names separated by the @ sign.

For our sample, we have a list of email IDs. You can split the usernames and domain names into different columns using the Other delimiters option.

Here again, you'll need to select the Other option as the delimiter on the second page of the configuring wizard.

  1. Select Other as the delimiter parameter.
  2. Uncheck the other delimiters.
  3. Enter the @ sign, in the box beside the Other option.
  4. Click the Next button once done.
    Using Other delimiter in MS Excel
  5. Use the Data Preview window to see how Excel will split the data.
  6. Choose the destination to save the output data and click on the Finish button. This would split the email address into usernames and domain names on separate cells.
    Entering destination cell for output data

Configure Text to Columns to Make Conversions Easier

You can customize the Text to Columns feature according to the data you want to split.

Text Qualifier

You can let Excel know where a string of text begins and ends with a Text Qualifier symbol. The Text Qualifier treats the data between two text qualifiers as one value irrespective of any delimiters within the text qualifiers. So if the cell value is "Neil, Armstrong", 12, and you specify the apostrophe as a text qualifier and the comma as the delimiter, then Excel will treat Neil Armstrong as one value and 12 as another value. Excel will ignore the comma between "Neil" and "Armstrong."

If no Text Qualifier is specified in the data, all the values will be split into different cells with the comma as the delimiter.

By using a text qualifier, you can tell Excel which symbols mark the beginning and the ending of the information you wish to import in Excel.

Data Preview

The Data preview box shows the final output as it will be separated into different columns on the sheet.

You can use this box to verify your output before you finish converting the text.

Treat Consecutive Delimiters as One

This option is beneficial if your data contains a delimiter of more than one character or if your data contains multiple delimiters. For example, if the cell value is Neil,; Armstrong ,; 12, you can choose to Treat Consecutive Delimiters as One.

If you don't check this option, MS Excel will consider Semicolon and Comma as two different delimiters and split the cells into five columns.

Thus, this feature will save you a lot of trouble when multiple delimiters are present.

Text qualifiers are vital when you want to analyze data that is cumbersome or complex. Delimiters help us to separate the data from one single cell.

After importing your data and decluttering it with delimiters, you might want to split data into separate sheets.

Organizing Data in Excel

Organizing data in MS Excel is a foundational skill. In data analytics, the first step towards making sense of data is by organizing it. And splitting data into separate cells is the first step to achieve this.

Comments

Popular posts from this blog

The Best 10 Social Media Platforms for Photographers to Flaunt Their Talent

Social media offers an excellent opportunity for photographers to connect with potential clients. In the digital era, it's a great asset. By showcasing your work on these networks, you can reach new audiences. Whether you are a professional or freelance photographer, the following social platforms will help you show off your work and get the right people to take notice... 1. Behance Behance is a classic portfolio publishing network that functions like a LinkedIn for creatives. Designed by Adobe, this is one of the best photography networking sites currently out there. The platform is ideal for sharing your portfolio and favorite images, allowing other Behance users to like and comment on your photos. By learning from their feedback and professional critiques, you can improve your work. The coolest feature of Behance is that it lets you find professional gig opportunities right on the platform. With your portfolio already available on the site, getting work becomes effortless.

The 6 Best Platforms for Sharing Your Digital Art Online

Whether you're looking for somewhere to host your digital art portfolio or simply want to share your latest artworks, it can be difficult to choose a website to upload to. Or at least, it definitely is more so than before, now that art websites aren't bubbling with as much excitement as they used to be. You know that each site has its pros and cons, but it's hard to figure out what those are unless you make an account and see for yourself. Don't worry if you don't have time for that—we've got your back. Here are the websites we recommend for sharing digital art, and why you might want to consider them. 1. Pixiv If you were around when the online art scene was ridiculously active, chances are that your art style is influenced by anime and/or manga in some way. Otaku culture began its slow sneak into mainstream media back then, and Pixiv is a great home for artists that fall in that category. Pixiv started as a small online community based in Japan, but has s

Snapchat Suspends Two Anonymous Messaging Apps Over Cyberbullying Claims

In light of a lawsuit that was filed earlier, two Snapchat apps, Yolo and LMK have been suspended by Snap. The apps allowed users to send anonymous messages on the platform. The Lawsuit Calls for an Immediate Ban of Yolo and LMK According to a LA Times report, the lawsuit was filed on behalf of Kristin Bride, the mother of a teen who committed suicide in 2020. The lawsuit alleges that Bride's son took his own life after being cyberbullied via Yolo and LMK. In addition to this, the lawsuit alleges that Yolo and LMK aren't doing enough to tackle cyberbullying, and have consequently violated consumer protection law as well as their own terms of service and policies. Both apps use Snap Kit, a set of tools that allows developers to directly connect to Snapchat for better integration features. Today the family of a 16-year-old Oregon boy who took his own life after being cyberbullied sued Snap and the makers of apps YOLO and LMK, alleging that the companies should be "h