Trending September 2023 # Split Cell In Excel (Examples) # Suggested October 2023 # Top 15 Popular

You are reading the article Split Cell In Excel (Examples) updated in September 2023 on the website Speedmintonvn.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 Split Cell In Excel (Examples)

Introduction to Split Cell in Excel

Split Cell in Excel means dividing a single cell’s data into multiple cells. It can be super useful when data from multiple columns or rows are included in a single cell. Splitting allows you to analyze and present the information more organized and meaningfully.

There are different ways to split cells in Excel, depending on what you want to achieve.

If you want to save time, then use a keyboard shortcut. You can select the data and press ALT + A + E keys to split cells simultaneously. You can also split a cell in Excel with fixed width if you want a specific length to break or use delimiters with special characters like commas, semicolons, spaces, etc.

In this article, we will learn various methods to split cells in Excel using different examples.

Example #1

You can download this Split Cell Excel Template here – Split Cell Excel Template

Split Merged Cells in Excel

This is the simplest method of splitting or Unmerging cells in Excel.

Result: All merged cells are split successfully, as seen below.

Note: Each merged cell’s content will be displayed on the upper-left cell, and all other merged cells will be empty.

Example #2 Split a Cell Diagonally in Excel

A Format Cells dialog box will appear.

Note: The” Border” preview section will display how the selected diagonal will appear. In the “Format Cells” dialogue box, you can apply multiple formatting per your demand to the selected cell.

We want to separate the heading (Year and Month) across the diagonal line.

A Format cells dialog box will appear.

“Month” appears at the top of the cell, as shown above. Now, Adjust the font size and alignment of “Year” and “Month“.

Result: Cell A1 is diagonally separated with “Year” representing rows and “Month” representing Columns.

Example #3 Split Cell in Excel Using Power Query

We can also split cells in Excel using Power Query. In a power query, a column’s data can be split into numerous columns per the requirement. We can split our data by delimiters, positions, numbers of characters, digit-to-non-digits, etc.

A “Create Table” dialog box will open.

Step 5: Select Space and press OK, as shown below.

Note: The first name and last name of the original data are separated by space.

A dialog box named Import Data will open.

Step 8: Change the column heading to “First Name” and “Last Name”.

Result: The first name and last name is separated using the power query.

Example #4 Split Cell Using Text-to-Column Function

We can also Split Cell in Excel using the text-to-column Function. Text to Columns function in Excel separates text strings by a specific delimiter such as comma, semicolon, space, and fixed character count.

A “Convert Text to Columns Wizard” will open.

Result: The data of column A is separated into three columns, as shown below.

Example #5 Split Cell in Excel Using Flash Fill

Another method to Split Cells in Excel is by Flash Fill. Flash Fill automatically populates cells with desired data. The Flash Fill method is easy to use and quickly splits data into multiple columns.

Flash Fill will automatically populate similar data (first name) into other cells. The entire column of B displays the first name of the original names.

Step 4: Repeat steps 3 on Cell C2 or select cell C2 and press “Ctrl + E“.

Note: The keyboard shortcut for Flash Fill is “Ctrl + E“.

Result: The first and second names are split successfully.

Example #6 Split Cell in Excel Using Text Functions

Although Flash Fill is easy to use, the results are not dynamic. It does not automatically update the output if the source data is changed. Thus to Split Cell in Excel, we can use various Text functions or create formulas to fetch results if we want dynamic results.

Solution:

Step 3: Now, select cell B2 and drag it down.

Note: The formula will display “#VALUE1″ if the original data contains no middle name.

Result: Using text functions, we have successfully separated the first, middle, and last names.

Note: Spilt Cell in Excel using the Text function is dynamic means if the value of the original data is changed, it will automatically get reflected in the output. For example, we have changed Matthew in Cell A2 to Soloman and James to John in Cell A6. The function automatically altered the value of Cell B2 to Soloman and Cell B6 to John, as shown below.

Step 9: Press “Enter“.

Result: The cells which show “#VALUE!” will be blank.

Things to Remember

The keyboard shortcut for unmerging cells is “Alt + H+ M + U“.

The shortcut for flash fill is “Ctrl+ E“. The result from Flash Fill is static.

The text functions in Excel are dynamic, which means if you change the values of the original data, it will automatically get reflected in the result.

Frequently Asked Questions(FAQs)

Answer: Split Cell in Excel is a feature to split or divide the content of single cells into numerous cells depending upon the task. For example, split cells are useful in separating first and last names.

Answer: There are various methods to Split Cell in Excel to extract data which are as follows:

Method

Description

Power Query Splits a column’s data into multiple columns based on delimiters, positions, characters, etc.

Text to Columns Splits the contents of a cell into two or more columns based on comma, semicolon, space, and fixed character count.

Text Functions Uses functions like MID, RIGHT, and LEFT for dynamic results.

Flash Fill Automatically splits strings into cells in the simplest way.

Answer: Merging of cells is joining two or more cells into a single cell. In contrast, splitting is the opposite of merging. Splitting cells means separating or dividing one cell content into two or more cells.