How to Split Cells in Excel?

How to Split Cells in Excel?

5 mins read19.5K Views Comment
clickHere
Rashmi
Rashmi Karan
Manager - Content
Updated on Oct 18, 2023 11:17 IST

Splitting cells in Excel involves dividing the contents of a single cell into multiple cells. This can be incredibly useful for organizing and formatting data in a way that makes it more accessible and understandable. Learn how to split cells in Excel through Excel built in functions and using formulas.

2022_06_Splitting-Cells-in-Excel-1.jpg

Microsoft Excel allows you to organize and manipulate data in various ways. When dealing with large datasets or information that needs to be segmented, the ability to split cells becomes a valuable tool. Splitting cells in Excel involves dividing the content of a single cell into multiple cells, which can be especially useful when you need to separate names, addresses, or other pieces of data currently combined in one cell.

Splitting in Excel is very simple. If you have multiple values in a single cell and want to separate them across multiple cells, then Microsoft Excel has some easy-to-use options. This guide will explore the techniques and methods to split cells in Excel, enabling you to efficiently manage and structure your data for better analysis and presentation.

Content

Read MS Excel Tutorials

How to Split Cells using Text to Columns

Splitting cells using the Text to Columns function is the easiest way. Let us check how to split a cell diagonally in Excel.

Below are some names we need to break into two parts.

  • Select all the cells that you want to split.
  • Locate Data ⇒ Data Tools ⇒ Text to Columns.
  • A Convert Text to Columns wizard will pop up. Choose the file type that is relevant to your data. In this case, we will choose Delimited.
  • In the next step, we will select the delimiter for our data set. Since Space separates the names, we will select Space. Depending on what delimiter the data set has, choose your option. In the data preview, you can see how the cell will be split.

Press Next to set the data format. Select General if you don’t want to apply any other advanced settings. 

You can choose Advanced settings if you are working on numeric data and want to recognize numeric data.

Since we don’t want to use additional settings, we will click Cancel and Finish while selecting General. 

The cell has now split into 2.

Most Popular and Powerful Formulas in Excel
Most Popular and Powerful Formulas in Excel
The article discusses the most popular formulas in excel through practical examples of the use of formulas for Excel.
What is Conditional Formatting in Excel?
What is Conditional Formatting in Excel?
Learn what is Conditional Formatting in Excel and explore different presets of Conditional Formatting to analyze and present your data in the most effective and powerful way.
Average Function in Excel – Formula and Examples
Average Function in Excel – Formula and Examples
The AVERAGE function is a popular Excel function used for various purposes, such as calculating average sales, average student scores, average monthly expenses, etc. It provides a quick and efficient...read more

Split Cells Diagonally

We have already seen how to split a cell in Excel and its contents. But next, we will teach you how to split an Excel cell diagonally. Don’t worry, it’s straightforward. Just follow these steps:

1. Select the cell you want to split diagonally, right-click and choose the Format Cells option, as the following image illustrates.

2. In the dialog box, go to the Border tab and click the button containing the diagonal artwork, and then click OK.

Once you’ve clicked OK, we’ll have our cell divided diagonally. 

Next, highlight the first word and go to the Fonts tab located in the Home tab’s bottom corner of the Fonts group.

In the dialog box, check the Superscript box and click OK. Then, repeat the same procedure for the other word, but you will put the Subscript option on this one and click OK.

As you can see, the two words are now split diagonally in the cell.

Top MS Excel Interview Questions
Top MS Excel Interview Questions
Master your Excel interview with confidence using our curated list of top questions. From basic functions to advanced data analysis, boost your Excel proficiency and shine in any data-driven role.
Introduction to MS Excel – A Beginner’s Guide
Introduction to MS Excel – A Beginner’s Guide
Let’s gets started with MS Excel, the most useful tool to crunch numbers and derive information for data analysts and scientists.
How to Create MIS Reports in Excel?
How to Create MIS Reports in Excel?
MIS reports are crucial to measure the business performance and contribute towards meeting the overall business goals. In this article, you will learn easy steps to create an MIS report...read more

Flash Fill for Splitting Cells  

You must know this commonly used Excel function. You must tell Excel how you want your data to split in flash fill.

We will go to –

Data ⇒ Data Tools ⇒ Flash Fill

Now follow the same process for the next part of the string.

Learn more about Flash Fill in Excel.

Excel Text Functions for Splitting Cells 

You can split a cell in Excel using different text functions. These text functions allow you to extract parts of a cell that you can send to another cell.

Text functions in Excel include:

Left() – To extract several characters from the left side of the text

Right() – To extract multiple characters from the right side of the text

Mid() – To extract multiple characters from the middle of a string

Find(): To find a substring within another string

len() – It returns the total number of characters in a text string

All these functions are not required to split cells, but there are specific ways you can use them in formulas to achieve the results.

For example, you can use the Left and Find function to extract the name. The Find feature helps because it can tell you where the delimiter character is. In this case, it is a space.

So the function would look like this:

=LEFT(A2,FIND(” “,A2))

When you press enter after typing this formula, you will see that the first name is extracted from the string in cell A2.

This works because the Left function needs the number of characters to extract. Since the space character is placed at the end of the name, you can use the LOOKUP function to find the space, which returns the number of characters you need to get the name.

You can extract the last name using the Right or Middle functions.

To use the Right function:

=RIGHT(A2,LEN(A2)-FIND(” “,A2))

This will extract the last name by finding the position of the space and then subtracting it from the total string length. This gives the Right function the number of characters it needs to extract the last name.

You can now apply the formula to the rest of the cells by dragging.

Hope this article was helpful.


Top Trending Articles in MS Excel:

Most Useful Excel Formulas | Min Max Functions in Excel | Average Functions in Excel | Introduction to MS Excel | Financial Modelling in Excel | MS Excel interview questions | Sum Function in Excel | Trim Function in Excel | Pivot Table in Excel | Percentage in Excel | Vlookup in Excel | Median Function in Excel | Types of Charts in Excel | Count Function in Excel | MS Excel Vs. Google Sheet | Remove Duplicates in Excel | Create Graph in Excel

Download this article as PDF to read offline

Download as PDF
clickHere
About the Author
author-image
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio

Comments

We use cookies to improve your experience. By continuing to browse the site, you agree to our Privacy Policy and Cookie Policy.