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.
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:
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:
Easy, right? It's amazing what you can do with only a few clicks!
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.
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:
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."
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)
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.
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(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?