How to Split Cells in Excel

Dividing the data from a single cell manually may be too tedious to do, and much more when you have a large column with information. What a nightmare! Take it easy and keep reading because here, you'll learn three methods by which you can split a cell in Excel.

"Text to Columns" command

This method can be convenient when having a lot of information, but it also works well if you want to split a few pieces of data. You only need to follow the steps below: 

1. First, select the range of cells that contains all the data to split. Ej: From A1 to A60.
2. Switch to the Data tab.
3. Go up to the right, where you will find the "Data Tools."
4. Click on "Text to columns."
5. Once in the wizard window, do the following:
   1. Click on the Delimited radio toggle: This button will help you to determine a specific character by which Excel will break the information in the cell.
   2. Click "Next"
   3. Now, you will see that the "Delimiters" option has displayed the possibilities you can pick to split the information:
      1. Tab.
      2. Semicolon.
      3. Comma.
      4. Space.
      5. Other (here, you can type the character you need).
   4. Click "Next"
   5. After all the above, you will see a title called "Column data format," which allows you to set the format of each next column as General, text, or Date. Finally, if you have a particular column you don't want to import, you must select the fourth option, "Do not import column (skip)."
   6. Set the "Destination" option: this one tells Excel from where the split columns can begin. By default, the data will always be at the first cell of the original data, which means the original column will be replaced. So if you want another destination, you must change the value to another one.
   7. Click finish.

"Flash Fill" command

This method is straightforward because you're basically telling Excel what the split data looks like: showing Excel how you want to split the information. So you only need to put the cell information at the left and then the split data in the following rows. After doing do the following steps:

1. Click on the last cell you've done and entered, 
2. Go to the "Data" tab.
3. Go to Data Tools.
4. Select the "Flash Fill" icon, which is the first one next to "Text to columns."
5. Repeat the steps for each column you have.

Easy, right? It's amazing what you can do with only a few clicks!

Formula

The last method to split the data string is using the Left, Mid, and Right formulas according to their position within the cell.

Note: This technique also works with data that are not even separated by spaces, such as numbers with prefixes included.

Left Formula

This function asks Excel to extract the principal leftmost characters you want. To do that, you need to know the syntaxis you can use for the cell:

=LEFT(TEXT, [NUM_CHARS])

• Text: the cell name.
• Num_Chars: refers to how many characters you need to extract from the chosen cell, starting with the first character you have on the very left side.

Example: 

You have this date, "Dic 22 2022," and you only want to extract the month, then your formula must be:

=LEFT(A1, 3) This means that you will take the first three characters from the A1 cell, and the result will be "Dic."

Mid Formula

This one asks excel how many characters take from the middle of the string data and from which one it can start. The syntaxis for this formula is:

=MID(TEXT, START_NUM, NUM_CHARS)

• Text: the cell name.
• Start_Num: The starting character number.
• Num_Chars: refers to how many characters you need to extract from the chosen cell, starting with the number of characters you chose.

Example: 

Continuing with the previous example, let's say you only want to extract the day of the date; then your formula must be:

=MID(A1,5,2), which means you will take the data from the fifth character of cell A1 and you will only take two characters so that the result will be: "22"

Note: The spaces also count as characters, so in the example, the character number was five, not to take the space before the number.

Right Formula:

This formula will take the rightmost characters from the cell you select, which means it will count the characters from right to left following the next syntaxis:

=RIGHT(TEXT, NUM_CHARS)

• Text: the cell name.
• Num_Chars: how many characters you will take from right to left.

Example:

=RIGHT(A1, 4), which means you will take the year corresponding to "2022" because those are the last four characters of the A1 cell.

There you have it, three excellent techniques to split your string data. Which one works best for you?