Mahbubur Rahman
Sometimes, we have more than one data available in one cell in Excel, for better interpretation and presentation we may feel the need to split these data. Excel has several functions and formulas for splitting data of one cell into columns. In this article, we will see how to split data in one Excel cell into multiple columns using some in-built Excel functions and formulas. We will use a dataset for your better understanding that contains cells with information.
Download Practice Workbook
5 Ways to Split One Data Cell into Multiple Columns in Excel
Throughout this whole article, we will get familiar with the operation of Text to Columns, Flash Fill, RIGHT Function, LEN Function, LEFT Function, and Fixed width.
Method 1: Split Data in One Cell into Multiple Columns Using Text to Column
So, we have a dataset containing the Full Name of the person, we want to split the First and Last Names into two different columns.
Steps:
- At first, we will select the data range, and go to Data > Text to Columns.
- As a result, a dialogue box will pop up, as the separator of our texts is space which is a kind of delimiter, so keep that as it is we will click Next.
- Now, we select Space and click Next.
- At this point, we have to select the location, where we want our split data, if we left the cell empty data split will start from that very first cell/column.
- Finally, preview in the Data preview section how our split data looks like and click Finish.
Full Name is separated into two different columns.
Read More: How to Split Data into Multiple Columns in Excel
Method 2: Split Data in One Cell into Multiple Columns by Fixed Width
Now, our dataset contains Two-digit age and Person’s name. Let’s see, how to split this data into multiple columns.
Steps:
- At first, we will select the data range, and go to Data > Text to Columns.
- A dialogue box will pop up, and as every name has two digits number we will consider it as Fixed. So, select Fixed width and click Next.
- After that, click on the position to fix the width you want to separate, and click Next.
- Now, select the location where we want our data, in this case, select $C$5 and Click Finish.
- At the end, we will get the final result as follows.
Read More: How to Split Data in Excel (5 Ways)
Method 3: Flash Fill to Split One Cell Data into Multiple Columns
Flash Fill is the coolest method available in Excel, which can be used to separate data into multiple columns.
Steps:
- First, type the first part of the name as it is in cell C5 in the column First Name.
- Press the ENTER key and drag it down to AutoFill rest of the series.
- Now, click the down arrow button as shown in the image, and select Flash Fill.
- As a result, our dataset will look like the following image.
- Now, we will follow this method for the Last Name, but this time, we will type the second part of the name in cell D5.
- After that, do drag and Flash Fill, our final result will look like the image below.
Read More: How to Split Data from One Cell into Multiple Rows in Excel (3 Methods)
Method 4: Using Formulas to Split One Data Cell into Multiple Columns
Here, we will use a combination function to separate first and last names in one cell into multiple columns.
Steps:
- First, type the following formula in cell C5.
=LEFT(B5,SEARCH(" ",B5)-1)
- Now, press the ENTER key.
Here, SEARCH(” “,B5)-1 function gives output 9, which is the number of characters Elizabeth has before a space. =LEFT(B5,9) will yield the final result Elizabeth.
- Finally, drag down to AutoFill rest of the series.
After that, for getting the last part of the name we need another set of functions’ combinations.
- First, type the following formula in cell D5.
=RIGHT(B5,LEN(B5)-SEARCH(" ",B5))
- Now, press the ENTER key.
LEN(B5)-SEARCH(” “,B5) will consider the last 5 letters after the space. =RIGHT(B5,5) will give the final output as Olsen.
- At last, drag down to AutoFill rest of the series.
Read More: How to Split Comma Separated Values into Rows or Columns in Excel
Method 5: Split a Cell Containing Multiple Lines into Multiple Columns
As you can see, we have a data set, where there are multiple lines in one cell. Each cell contains three types of information. Let’s split this data.
Steps:
- First, select the data, and go to Data > Text to columns.
- At this point, a dialogue box will pop up, and click Next.
- After that, select Other: and press CTRL+J in the box shown in the image. After that, click Next.
- Set the destination. Finally, click Finish.
Our Final result will look like this.
Practice Section
The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, we’ve attached a practice workbook where you may practice these methods.
For practicing, multiple lines in one cell.
Conclusion
That’s all for the article. These are 5 different methods on how to split data in one Excel cell into multiple columns. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback.
