MS Excel is a go-to software for someone who enters data. If you want to apply for a data entry job you should be an expert in MS Excel. Not only many businesses nowadays rely on management via Computer, one thing that you will see them doing is using MS Excel for this purpose.
Many times, you might have received data that is not in the desired format. This is the most applicable to the data that was being imported to Excel.
In that case, the only way you can make that data applicable for Excell is by splitting the cell. In this article, we are going to discuss different methods and techniques by the virtue of which one can easily split cells.
Ways To Split a Cell in MS Excel
1. Use the Text to Column tool to split the cell
Text to column method is an OG when it comes to splitting the cell in MS Excell. With the help of this tool, one can easily split the entire column in just one go. This is one of the easiest methods that we are going to talk about in this article. So, let’s see step-by-step how it works.
We are going to split the “name” column in this article. From this “Wage Record,” we are going to split the name cell into two parts one will contain the first name of the person and the other will have the surname.
To split the cell with the help of the Text to Column tool you need to be obedient to the following steps.
Step 1: Select the entry you want to split, in this case, I am going to split a name.
Step 2; Afterward, under the data tab, click on Text to column placed at the right side of the ribbon.
Step 3: From the window that has appeared, select Delimited, and move forward by hitting Next.
Step 4: Now, untick every option from the list except Space and click on Next.
Step 5: In the next window, select the destination and click finish to end the task.
As you can see the cell has split and you are good to go. One thing that you should always keep in mind that the text must be separated by a “space”, if you are choosing “space”, otherwise, you can choose semicolon, comma, and tab if your text contains them.
2. Use text function to split the cell
Text Functions are a code/function, that is used to return a value in MS Excell. Text Function can do a lot of things in MS Excell and Splitting the cell is one of them. Therefore, in this section, we are going to use the Text Function to split the cell.
Before going any further let’s have a quick glance at the cells that we have:-
- Left(): This command is used to abstract a string from the left side of the text.
- Right(): This command is used to abstract a string from the right side of the text.
- Mid(): This command is used to abstract a string from the middle of the text.
- Find(): This command is used to find a text placed within a string.
- Len(): This command is used to copy the complete length of the character.
These functions will be used to split the cell. But you can’t just write these functions and wait for a miracle to happen. We need to fill the parenthesis with the location of that cell we are going to split.
For example, if we want to extract the first name of a person, then we ought to write
=LEFT(B3,FIND(“ “,B3))
As you can see in the example printed above that we are able to extract the first name which is Sam from the whole string of Sam Edbert.
Now, let’s find out the meaning behind this syntax, as you can LEFT() is used because we are going to extract the string from the left side of the text. B3 is used as it defines the location of the text in the sheet.
B3 is followed by the FIND keyword which is there to check the delimit which is encapsulated by the parenthesis. Finally, we have “ “ which is used to tell the code that the string that it’s going to extract is differentiated by a text.
Now, how will you extract the last name? It is not as simple as it was in the case of extracting the first name.
There are two methods that allow you to extract the last name of the person. One of which is with the help of RIGHT() and the other one is MID().
Let’s extract the last name with the help of the Right() function first. Its syntax is =RIGHT(B3,LEN(B3)-FIND(“ “,B3))
Now as you can see in the example here that we have extracted “Edbert” which is the last name from the text.
If we look at the code/function we will see that since we have to extract the string from the right side of the string, we used RIGHT().
In the parenthesis, we have mentioned the location B3, which is followed by LEN(B3)-FIND(“ “,B3) which means that we the length of the complete string of B3 is subtracted by the string that is on the other side of the <SPACE> which is represented by “ “ and then the parenthesis is closed to return the value and you are good to go.
Let’s do the same with the help of the Mid() function.
The syntax of which is =MID(B3,FIND(“ “,B3),LEN(B3)-FIND(“ “,B3))
As you can see the example here. The result of the previous and later is the same.
Now, let’s understand the Syntax carefully. The MID function is used to extract the string from the middle of the text.
If we look inside the parenthesis, we will see that the code is finding <SPACE> from the string which is then combined with the LEN() function. That will calculate the length of the string in B3. Finally, FIND(“ “,B3) is used to space between the text and delete the reluctant text.
3. Use Flash Fill to Split Cell
As we have already discussed various methods by which you can split a cell. All of them are good but what if you want to split a whole column, don’t you think that will be too long of a process? If your answer is yes, then let me introduce you to the Flash Fill feature bundled MS Excel.
The best thing about the Flash Fill feature is that it automates the process and reduce the time consuming manual labor. You just need to do a couple of manual twitches and leave the rest on the strong shoulders of Excel’s Flash Fill.
To Split Cell with the help of Flash Fill use the following steps:-
Step 1: Insert a text from the column that you want to extract the value from. For example, we are going to extract the first name of the employee, therefore we are going to type the first name in front of the ROW consisting of the full name.
Step 2: Now, click on the newly written name and drag it down to the end of the table.
Step 3: Finally, go to the Data tab and click on Flash Fill. And you are good to go.
As you can see in the example below that the task is accomplished.
The Flash Fill is the smartest tool in MS Excell, it can automatically detect the text and the work it is assigned and then proceed accordingly.
Flash Fill is especially used when you need to split the entire column and not just a single entry. Using the other methods such as text to Column and Text function will take ages.
Summary:-
Let’s wrap up the topic with the help of these few points:-
- MS Excel is a data entry tool introduced by Microsoft. It is used for calculation, doing graphical representation, etc.
- To split cell with the help of the Text to Column tool Select the entry, under the Data tab, click on Text to Column. From the appeared, window Delimited>Next>(choose the delimit, space in this case)>Next>Select the location>Finish.
- To split cell with the help of Text Functions, if you want to extract the left string in a text then you have to use =LEFT(B3,FIND(“ “,B3)), for the right string you have two options, you can either use =RIGHT(B3,LEN(B3)-FIND(“ “,B3)) or =MID(B3,FIND(“ “,B3),LEN(B3)-FIND(“ “,B3)) they both will do the same purpose.
- To Split cell with the help of Flash Fill is very easy. You need to Insert a text from the column that you want to extract the value from, then data tab>Flash Fill.
