Làm Cách Split Cell Trong Excel ? Bài 9: Split Cell

Nhấn vào chỗ này để sở hữu về miễn giá tiền Kutools for Excel

Nếu bạn có nhu cầu chia chuỗi theo độ lâu năm văn bạn dạng cụ thể, bạn cũng có thể sử dụng Kutools for Excel"S Chia ôchức năng s.

Sau khi thiết lập Kutools for Excel, vui vẻ làm như dưới đây:(Tải xuống miễn tầm giá Kutools for Excel hiện nay nay!)

Giả sử bạn có một danh sách những chuỗi số và bạn có nhu cầu chia các số thành các ô lẻ tẻ như hình minh họa mặt dưới:

Các phép tắc năng suất văn phòng tốt nhất

You may need khổng lồ split a value in your cell into two or more columns by space, character, or another delimiter. This can be done easily with a few clicks. On the other hand, some users need to lớn split cells into rows. Although Excel does not have this functionality out of the box, you can use a few functions to do the job. Read on khổng lồ learn all the options available for splitting cells in Excel.

How bởi vì I split cells in Excel?

You can split cells in Excel with the following options:

For this article, we have decided to group the use cases not by option, but by the expected outcome. So, we’ll cover two epic tasks: split cells to lớn columns and split cells to rows. Each of these tasks will include the options to lớn split by different delimiters và other specific requirements.

For the examples below, we imported data from different sources such as Airtable, Google Sheets, and Big
Query with emcanbaove.edu.vn.io. It’s a powerful solution for getting data lớn Excel from multiple apps and web APIs without coding. The main benefit is that you can synchronize your workbook with your data source on a custom schedule!

Check out the Microsoft Excel integrations available.

Let’s kiểm tra out each option so you can choose the one that works best for your task.

How lớn split one cell into two cells in Excel

We’ll warm up with a simple example of splitting a full name, Margeaux Kuhnke, into first & last name cells. Go khổng lồ the Data ribbon và click Text khổng lồ Columns.

A wizard will open, where you’ll need to lớn perform three steps:

Once you click “Finish“, your cell will be split into the number of cells depending on the number of delimiters in your source value. In our example, we had one delimiter, so the cell was split into two cells.

Split text in Excel cell into multiple cells by space

If a full name in a cell consists of three or more names, the number of split cells will be greater. For example, let’s split Pablo Picasso’s full name using space as a delimiter:

Pablo Diego José Francisco de Paula Juan Nepomuceno María de los Remedios Cipriano de la Santísima Trinidad Ruiz y Picasso

Excel split cells by character

Splitting cells by character may be useful if you need to separate, for example, username và domain name in thư điện tử addresses. Let’s say we have a fictional user with the following e-mail address:

elon_musk_2021
. So, we need to lớn specify this.

There you go!

Excel split data in cell by line break

What if your cell contains a few lines like an address, & you want each line in its own cell?

8054 Old Shore Parkway
Houston
United States
To split this string, you need to specify the line break as a delimiter. To vì chưng this, choose Other as a delimiter và press Ctrl + J.

Here is the result:

How khổng lồ split merged cells in Excel

Splitting merged cells is a slightly different concept, since it does not affect the content of the cells. You can do this easily using the Merge và Center button.

Select the merged cells, mở cửa the drop-down options và click Unmerge Cells.

Split cells into rows in Excel

Unfortunately, there is no single button or function lớn split a cell into rows or to lớn split cells vertically in Excel. Nevertheless, you can bởi vì this with the help of Excel functions or power Query. But first, kiểm tra out this easy method for splitting cells into rows.

Split Excel cell into two rows easily

You’ll need to lớn take two steps:

This is the simplest way to split cells khổng lồ rows in Excel, but it’s manual. Let’s discover some options khổng lồ automate the flow.

Split cells vertically in Excel with formulas

Let’s take the previous example of splitting a full name: Margeaux Kuhnke.

We’ll need two formulas for each row:

= LEFT(cell, FIND(" ",cell) - 1)This formula extracts a string before the specified delimiter: FIND(" ",cell).

2. Second

= RIGHT(cell, LEN(cell) - FIND(" ",cell))This formula extracts a string after the specified delimiter.

If you have two or more delimiters, both formulas will only work with the first delimiter, like this:

If for some reason you need to split a cell by a second or third delimiter, you need khổng lồ update the FIND formula, keeping the following xúc tích in mind:

=FIND("delimiter",cell,starting_position)starting_position – specify the starting position (character) to skip the unnecessary delimiters.In our example, lớn split the cell by the second delimiter, the formulas will look lượt thích this:

=LEFT(A1,FIND(" ",A1,10)-1)=RIGHT(A1, LEN(A1) - FIND( " ",A1,10) )

Split cells in Excel into multiple rows

=FILTERXML("" &SUBSTITUTE($cell," ", "") và "", "https://s")This converts cell contents into an XML string and then extracts data from it. This formula should be executed as an array formula; that is, you need lớn select the cell lớn split into và press Ctrl+Shift+Enter. Here is how it works with the Pablo Picasso’s full name:

=FILTERXML("" &SUBSTITUTE($A$1," ", "") & "", "https://s")You can also use this formula lớn extract specific pieces of your string by specifying them as follows:

=FILTERXML("" &SUBSTITUTE($cell," ", "") và "", "https://s")For example, to extract the third name from the Picasso’s full name, we update our formula as follows:

=FILTERXML("" &SUBSTITUTE($A$1," ", "") & "", "https://s<3>")

How khổng lồ split cells to lớn rows or columns in Excel dynamically without formulas

There you go!

Now, every value added lớn the A column will be split into rows by space. Just click refresh to see the split values.

See, with nguồn Query you can also split cells into columns by different delimiters.

Bonus: How to split cells diagonally in Excel

By diagonal split, users usually mean crossing a cell with a diagonal border lượt thích this:

This does not split a cell into columns or rows, but makes a visual split of two values in the cell. Here is how you can bởi this.

Xem thêm: Cách Làm Văn Bản Trên Excel Đẹp Như Word, Cách Trình Bày Văn Bản Trong Excel Đẹp Như Word

Are there other options khổng lồ split cells in Excel?

In Excel, there is no dedicated function for splitting data, like SPLIT in Google Sheets. However, using Excel VBA, you can build a custom VBA function lớn solve your specific splitting tasks. This requires knowledge of Microsoft Visual Basic for Applications.

However, for most cases, the options previously described will vị the job. Good luck with splitting your data!