How To Split Cells In Excel?

How To Split Cells In Excel?

Microsoft Excel is a powerful tool that can be used to organize and manipulate data differently. The ability to split cells becomes a valuable tool when dealing with large datasets. To split the cell in Excel, you can use Text to Column Wizard, Excel formulas, power Query, Flash fill, Text function, Fixed width option, multiple cells, etc. When the data is entered with many points in one cell, which are used in different purposes, then we use splitting cells in Excel.   

In this article, we will learn how to split cells in Excel using formulas, shortcut keys, and more.  

Become an Expert in MS Excel, and join our Office Master workshop at just Rs 9.              

What are Split cells in Excel?

Why do we need to split the cell in Excel? We need to split the cell in Excel to divide the data within a single cell into a separate cell. Split cells in Excel is a software tool that enables users to change the format of their spreadsheet. This operation allows the user to divide text from one column and arrange it into two new columns.

Users can split cells by selecting a delimiter option, separating information by space or punctuation, such as a comma, period, or dash. They can also manually separate their cells using Excel’s fixed width option. It assists in extracting and organizing information in a structured way to make it easier to analyze and present the data. We have to separate the cells to process the text.  

Want to learn how to split cells in MS Excel from Master Office at just Rs 9? 

Different ways to use Split Cell in Excel 

Various ways you can use to split cells in Excel are:

  • To split cells using a text-to-column wizard in Excel
  • To split cells using Excel formulas in Excel
  • To split cells using Flash fill in Excel 
  • To split cells using Power Query in Excel 
  • To split cells using the Text function
  • To split cells using the fixed width option in Excel 
  • To split a merged cell into multiple cells in Excel
  • To split multiple lines into multiple rows in Excel 

Learn different ways to split cells in MS Excel from the Office Master workshop at just Rs 9.  

To split a cell in Excel using Text to column Wizard 

Let’s learn how to split the cell in Excel using the Text to Column Wizard:

Step 1: First, select the data you want to split.

Split Cells In Excel

Step 2: Go to the Data tab in the ribbon and click on the text-to-column option in the Data tools to open the TEXT TO COLUMN wizard in a dialog box.

Split Cells In Excel

Step 3: The dialog box appears. Select the “Delimited” and Press “Next”. 

Split Cells In Excel

Step 4: Tick the “Space” text box present in the Delimiters and click “Next”. 

Split Cells In Excel

Step 5: Change the Destination to $B$1 cell and click on the “Finish” option. 

Split Cells In Excel

You will see the output like this. 

Split Cells In Excel

You can see how it separates the Names and Surnames of the students. 

To split a cell in Excel using Excel Formulas

Ok! After successfully splitting a single cell’s data into two columns, we’ll see how to split a cell in Excel using Excel formulas.

Do you remember your Excel sheet look like this: 

Split Cells In Excel

Now we will see how to separate this with Name and Surname in different columns:

Step 1: Type the Headers “Name”  and “Surname” in two different columns, B2 and C2, respectively.

Step 2: Write the formulas below in B3 to split the cell into two parts.

=LEFT(A2, FIND(“ “,A2)-1)

Step 3: Fill the formula in the cell from B2 to B11. 

Step 4: Write a similar formula in cell C2 to split the last words. 

Formula: =RIGHT(A2,LEN(A2)-FIND(“ “,A2))

Step 5: Now fill this formula from C2 cell to C11. 

 Finally, look at the table results.

To split a cell in Excel using Power Query 

Let’s learn how to split the cell in Excel Using Power Query:

Step 1: Select the data you want to separate.

Split Cells In Excel

Step 2: Click on the Data Tab. From “Get and Tranform Data” section choose the option “From table/Range”. A dialog box appear. 

Step 3: Go to the home tab in the power query dialog box and choose the split cell option.

Step 4: On the drop-down arrow, click on the delimiter option. 

Split Cells In Excel

Step 5: From the dialog box, choose the required option and press ok.

To split a cell in Excel using Flash Fill

Splitting a cell using a flash fill is done in two ways.”

  • In a background
  • Triggered manually by the user

In a background: 

In this, Excel offers ideas after trying to identify trends in the text.

  1. Type the part of the text element that you want to extract from the other column. As you enter the text, it flashes fill out, offering suggestions for the whole text. 
  2. If it is the right suggestion, press the Enter key or Tab Enter to accept it and proceed to the next step. 

Manually by the user: 

Now, we’ll see how Flash Fill auto-suggests the data. Let’s look at the user’s manual approach.

  • In the consecutive row, type the text element you want to extract. See in this particular example:

  • Click on the Home tab. In the Editing section, go to the Fill option. Click on the Flash fill option in the drop-down arrow. 

To split a cell in Excel using the Text Function:

There are many text functions in Excel, but we don’t need all of them, and most of the time, we don’t know how to use them. But now let’s explore one by one how we can use them:

  • LEN: This function is used to return the length of the string.
  • RIGHT: This function extracts the defined number of characters from the right end of the String.
  • LEFT: This function extracts the defined number of characters from the left end of the string. 
  • FIND: It will look like a string inside another string.
  • SEARCH: It will return the position of the string inside another string. 
  • MID: This function extracts the defined number of characters from the Specified string’s center.

Do you remember the above example of the function of extracting the left part of the string?

The formula is the combination of these functions. This will help to extract the data you require. 

In this guide, I hope you will understand the maximum steps of splitting cells in Excel step by step. It will be a practical guide for formatting the cells. If you want to learn more, you can go through the Master Office workshop. 

Office Master is one of the top Excel training programs. It offers an opportunity to become an Excel master and increase your career potential in Data Science, Data finance, Data research, Business analytics, and so on. 

Mastering an Excel tool makes you an asset to any industry or business. Every industry uses Excel, and for the sake of the same, they require an Excel Master. 

Office master workshops are led by the AI tool industry experts Mr Aditya Goenka and Aditya Kachave.. As I passed out from such a prestigious college, IIT Kharagpur, there is no doubt about their qualifications. They have built two eight-figure companies with the help of AI tools and had a super-effective team of just five people. They came up with the knowledge and experience with you to help you increase your productivity using AI tools. 

Join now and unlock your true potential with Office Master at Rs 9.

Want to become a certified MS Excel master? You can get a certificate of Office mastery that you can use anywhere in the world.   

This course is for you if you want to learn an advanced live MS Excel Workshop using AI and chatbots.

  • You can create all your Excel Reports in just 60 Seconds. 
  • You can elevate your efficiency with 115 automation templates.
  • You can integrate Chatgp and AI inside Excel and build your reports in seconds.
  • You can seamlessly automate 35 tasks with Excel Macros, and you will learn to automate 42 common Indian job tasks.  
  • You can master 200+ Excel formulas and unlock 27 hidden Excel formulas. 

If you enroll now, you get a chance to get bonuses worth Rs 10500/-

 

Leave a Comment

Your email address will not be published. Required fields are marked *