How to Split Data in One Excel Cell into Multiple Columns (5 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

Split Data in One Excel Cell into Multiple Columns


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.

Split Data in One Excel Cell into Multiple Columns 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.

How to Split Data in One Excel Cell into Multiple Columns Text to columns

  • 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.

How to Split Data in One Excel Cell into Multiple Columns Text to columns

  • 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.

Split Data in One Excel Cell into Multiple Columns by fixed width

  • 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.

Split Data in One Excel Cell into Multiple Columns by fixed width

  • 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.

Split Data in One Excel Cell into Multiple Columns by fixed width

  • At the end, we will get the final result as follows.

Split Data in One Excel Cell into Multiple Columns by fixed width

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.

How to Split Data in One Excel Cell into Multiple Columns by flash Fill

  • Press the ENTER key and drag it down to AutoFill rest of the series.

How to Split Data in One Excel Cell into Multiple Columns by flash Fill

  • 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.

How to Split Data in One Excel Cell into Multiple Columns by flash Fill

  • 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)

How to Split Data in One Excel Cell into Multiple Columns using formula

  • 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))

How to Split Data in One Excel Cell into Multiple Columns using formula

  • 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.

How to Split Data in One Excel Cell into Multiple Columns using Other option

  • 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.

How to Split Data in One Excel Cell into Multiple Columns containing lines in cell

  • Set the destination. Finally, click Finish.

Our Final result will look like this.

How to Split Data in One Excel Cell into Multiple Columns containing lines in cell


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.


Related Articles

Mahbubur Rahman
Mahbubur Rahman

Mahbubur Rahman is a highly skilled and experienced professional with a strong background in both engineering and business administration. With an impressive technical skill set and a passion for innovation and problem-solving, Mahbubur has achieved great success in his field.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo