如果您在Excel中处理从其他来源导入的数据，有时您必须处理不是您想要的格式的数据。对于进入单个单元格的逗号分隔文本尤其如此。
处理该数据的唯一方法是在Excel中拆分一个单元格。有不同的方法可以做到这一点，具体取决于数据的格式。
在本文中，您将学习如何拆分单元格，如何将其展开到整列，以及何时应该选择每个选项。
将文本转换为列(Convert Text To Columns)
在Excel(Excel)中拆分单元格的最常用方法之一是使用“文本到列(Columns)”工具。这使您可以使用您喜欢的任何规则拆分整列单元格。
该功能还包括一个易于使用的向导，这就是大多数人喜欢使用它的原因。它还处理任何文本格式，无论分隔文本是空格、制表符还是逗号。
让我们看一个如何在 Excel 中使用“文本到列”功能的示例。(Text to Columns feature)
在此示例中，我们要将 Name 列拆分为两个单元格，即销售人员的名字和姓氏。
去做这个：
1. 选择数据(Data)菜单。然后在功能区的数据工具(Data Tools)组中选择文本到列。(Text to Columns)
2. 这将打开一个三步向导。在第一个窗口中，确保选中Delimited并选择Next。
3. 在下一个向导(Wizard)窗口中，取消选择Tab并确保选中Space。选择下一步(Next)继续。
4. 在下一个窗口中，选择目标(Destination)字段。然后，在电子表格中，选择您希望名字所在的单元格。这会将目标(Destination)字段中的单元格更新为您选择的位置。
5. 现在，选择完成(Finish)以完成向导。
您会看到包含名字和姓氏的单个单元格已被拆分为两个单独包含每个单元格的单元格。
注意(Note)：上述过程有效，因为要在单元格中拆分的数据有一个空格分隔文本。如果文本由制表符、分号、逗号或您指定的任何其他字符分隔，则此文本到列功能还可以处理在Excel中拆分单元格。(Excel)
使用 Excel 文本函数(Use Excel Text Functions)
在Excel(Excel)中拆分单元格的另一种方法是使用不同的文本函数(text functions)。文本函数让您可以提取可以输出到另一个单元格的单元格片段。
Excel 中的文本函数包括：
- left ()：从文本左侧提取多个字符
- right ()：从文本右侧提取多个字符
- mid ()：从字符串中间提取若干个字符
- find ()：在另一个字符串内部查找一个子字符串(Find)
- Len ()：返回一串文本的字符总数
要拆分单元格，您可能不需要使用所有这些功能。但是，您可以通过多种方式使用它们来完成相同的事情。
例如，您可以使用Left和Find函数来提取名字。Find功能很有帮助，因为它可以告诉您分隔符在哪里。在这种情况下，它是一个空间。
所以函数看起来像这样：
=LEFT(C3,FIND(” “,C3))
键入此函数后按 Enter 键时，您会看到名字是从单元格 C3 中的字符串中提取的。
这是可行的，因为Left函数需要提取的字符数。由于空格字符位于名字的末尾，因此您可以使用FIND函数查找空格，它返回获取名字所需的字符数。
您可以使用Right(Right)函数或Mid函数提取姓氏。
要使用 Right 功能：
=RIGHT(C3,LEN(C3)-FIND(” “,C3))
这将通过查找空格的位置来提取姓氏，然后从总字符串的长度中减去它。这为 Right 函数提供了提取姓氏所需的字符数。
从技术上讲，您可以使用Mid函数执行与(Mid)Right函数相同的操作，如下所示：
=MID(C3,FIND(” “,C3),LEN(C3)-FIND(” “,C3))
在这种情况下，Find函数为Mid函数提供了起点，而Len与Find结合提供了要提取的字符数。这也将返回姓氏。
使用Excel文本函数在Excel中拆分单元格与Text-To-Column解决方案一样有效，但它还允许您使用相同的函数填充这些结果下方的整个列。
使用 Flash Fill 在 Excel 中拆分单元格(Split Cell in Excel Using Flash Fill)
在Excel(Excel)中拆分单元格的最后一个选项是使用Flash Fill 功能(Flash Fill feature)。这要求您将原始单元拆分成的单元格就在其旁边。
如果是这种情况，您所要做的就是键入要拆分的原始单元格的一部分。然后向下拖动单元格的右下角以填充其下方的单元格。执行此操作时，您会看到一个小单元格填充图标，旁边有一个小加号。
选择此图标，您将看到一个菜单弹出窗口。在此菜单中选择Flash Fill 。
执行此操作时，您会看到Flash Fill功能会自动检测您键入内容的原因，并将在下一个单元格中重复该过程。它将通过检测并填写左侧原始单元格中的名字来完成此操作。
当您填充整个列时，您实际上可以执行相同的过程。选择相同的图标并选择Flash Fill。它将使用左侧单元格中的正确名字填充整个列。
然后，您可以复制整个列并将其粘贴到另一列中，然后重复相同的过程以提取姓氏。最后，将整列复制并粘贴到电子表格中您想要的位置。然后删除您用于执行快速填充(Flash Fill)过程的原始列。
在 Excel 中拆分单元格
如您所见，有几种方法可以完成相同的事情。如何在Excel中拆分单元格归结为您希望最终结果的位置以及您打算如何处理它。任何选项都有效，因此请选择最适合您的情况的选项并使用它。
3 Ways To Split a Cell In Excel
If you’re working with data in Excel that you’ve imported from other sources, sometimeѕ you have to work with data that isn’t in a format that you want. Τhiѕ is espeсially true with comma-delimited text that cоmes into sіngle cells.
The only way to deal with that data is to split a cell in Excel. There are different ways to do this, depending on the format of the data.
In this article you’ll learn how to split a cell, how to roll that out to an entire column, and when you should choose each option.
Convert Text To Columns
One of the most common methods to split a cell in Excel is using the Text to Columns tool. This lets you split an entire column of cells using whatever rules you like.
The feature also includes an easy-to-use wizard, which is why most people prefer using it. It also handles any text format, whether the separating text is a space, a tab, or a comma.
Let’s look at an example of how to use the Text to Columns feature in Excel.
In this example, we want to split the Name column into two cells, the first name and the last name of the salesperson.
To do this:
1. Select the Data menu. Then select Text to Columns in the Data Tools group on the ribbon.
2. This will open a three-step wizard. In the first window, make sure Delimited is selected and select Next.
3. On the next Wizard window, deselect Tab and make sure Space is selected. Select Next to continue.
4. On the next window, select the Destination field. Then, in the spreadsheet, select the cell where you want the first name to go. This will update the cell in the Destination field to where you’ve selected.
5. Now, select Finish to complete the Wizard.
You’ll see that the single cell that contained both the first name and last name has been split into two cells that contain each one individually.
Note: The process above works because the data to split in the cell had a space separating the text. This text-to-column feature can also handle splitting a cell in Excel if the text is separated by a tab, semicolon, comma, or any other character you specify.
Use Excel Text Functions
Another way to split a cell in Excel is by using different text functions. Text functions let you extract pieces of a cell that you can output into another cell.
Text functions in Excel include:
- Left(): Extract a number of characters from the left side of the text
- Right(): Extract a number of characters from the right side of the text
- Mid(): Extract a number of characters from the middle of a string
- Find(): Find a substring inside of another string
- Len(): Return the total number of characters in a string of text
To split cells, you may not need to use all of these functions. However, there are multiple ways you can use these to accomplish the same thing.
For example, you can use the Left and Find function to extract the first name. The Find function helps because it can tell you where the delimiting character is. In this case, it’s a space.
So the function would look like this:
=LEFT(C3,FIND(” “,C3))
When you press enter after typing this function, you’ll see that the first name is extracted from the string in cell C3.
This works, because the Left function needs the number of characters to extract. Since the space character is positioned at the end of the first name, you can use the FIND function to find the space, which returns the number of characters you need to get the first name.
You can extract the last name either using either the Right function or the Mid function.
To use the Right function:
=RIGHT(C3,LEN(C3)-FIND(” “,C3))
This will extract the last name by finding the position of the space, then subtracting that from the length of the total string. This gives the Right function the number of characters it needs to extract the last name.
Technically, you could do the same thing as the Right function using the Mid function, like this:
=MID(C3,FIND(” “,C3),LEN(C3)-FIND(” “,C3))
In this case the Find function gives the Mid function the starting point, and the Len combined with Find provides the number of characters to extract. This will also return the last name.
Using Excel text functions to split a cell in Excel works as well as the Text-To-Column solution, but it also lets you fill the entire column beneath those results using the same functions.
Split Cell in Excel Using Flash Fill
The last option to split a cell in Excel is using the Flash Fill feature. This requires that the cells you’re splitting the original one into are right beside it.
If this is the case, all you have to do is type the part of the original cell that you want to split out. Then drag the lower right corner of the cell down to fill the cell beneath it. When you do this, you’ll see a small cell fill icon appear with a small plus sign next to it.
Select this icon and you’ll see a menu pop-up. Select Flash Fill in this menu.
When you do this, you’ll see that the Flash Fill feature automatically detects why you typed what you typed, and will repeat the process in the next cell. It’ll do this by detecting and filling in the first name in the original cell to the left.
You can actually do this same procedure when you fill the entire column. Select the same icon and select Flash Fill. It’ll fill the entire column with the correct first name from the cells to the left.
You can then copy this entire column and paste it into another column, then repeat this same process to extract the last names. Finally, copy and paste that entire column where you want it to go in the spreadsheet. Then delete the original column you used to perform the Flash Fill process.
Splitting Cells in Excel
As you can see there are a few ways to accomplish the same thing. How you split a cell in Excel boils down to where you want the final result to go and what you plan to do with it. Any options works, so choose the one that makes the most sense for your situation and use it.
