如果您经常使用Excel，您可能会遇到这样一种情况，即您在单个单元格中有一个名称，并且您需要将名称分隔到不同的单元格中。这是Excel中一个非常常见的问题，您可能可以进行Google 搜索并下载(Google search and download)由不同人编写的 100 个不同的宏来为您执行此操作。
但是，在这篇文章中，我将向您展示如何设置一个公式，以便您自己完成并真正了解发生了什么。如果您经常使用 Excel，那么学习一些更高级的功能可能是个好主意，这样您就可以对数据做更多有趣的事情。
如果您不喜欢公式并想要更快的解决方案(quicker solution)，请向下滚动到“文本到列(Text to Columns)”部分，该部分将教您如何使用Excel 功能(Excel feature)来做同样的事情。此外，如果单元格中有两个以上的项目需要分隔，则文本到列功能也更适合使用。例如，如果一列有 6 个字段组合在一起，那么使用下面的公式(formulas below)将变得非常混乱和复杂。
Excel 中的单独名称
首先，让我们看看名称通常是如何存储在Excel 电子表格(Excel spreadsheet)中的。我见过的最常见的两种方式是firstname lastname只有一个空格和lastname，firstname用逗号分隔两者。每当我看到中间名首字母时，它通常是名字(firstname) 中间名(midinitial) 姓氏(lastname)，如下所示：
使用一些简单的公式并将它们组合在一起，您可以轻松地将名字、姓氏和中间名首字母分隔到Excel中的单独单元格中。让我们从提取名称的第一部分开始。在我的例子中，我们将使用两个函数：left 和 search(left and search)。从逻辑上讲，这是我们需要做的：
(Search)在单元格中的文本中搜索空格或逗号(space or comma)，找到该位置，然后取出该位置左侧的所有字母。
下面是一个可以正确完成工作的简单公式：=LEFT(NN, SEARCH(” “, NN) – 1)，其中 NN 是其中存储有名称的单元格。-1 用于删除字符串末尾的多余空格或逗号。(space or comma)
如您所见，我们从left 函数(left function)开始，它有两个参数：字符串和您想要从字符串开头抓取的字符数。在第一种情况下，我们通过使用双引号并在其间放置一个空格来搜索空格。在第二种情况下，我们正在寻找逗号而不是空格。那么我提到的 3 个场景的结果是什么？
我们从第 3 行(row 3)获得名字，从第 5 行(row 5)获得姓氏，从第 7 行(row 7)获得名字。伟大的！因此，根据您的数据的存储方式，您现在已经提取了名字或姓氏。现在进行下一部分。这是我们现在需要在逻辑上做的事情：
–在单元格中的文本中搜索(Search)空格或逗号(space or comma)，找到位置，然后从字符串的总长度中减去该位置。这是公式的样子：
=RIGHT(NN,LEN(NN) -SEARCH(” “,NN))
所以现在我们使用正确的功能。这也需要两个参数：字符串和要从左侧字符串末尾开始抓取的字符数。所以我们想要字符串的长度减去空格或逗号(space or comma)的位置。这将为我们提供第一个空格或逗号(space or comma)右侧的所有内容。
太好(Great)了，现在我们有了名称的第二部分！在前两种情况下，您已经完成了很多工作，但是如果名称中有一个中间名首字母，您可以看到结果仍然包括带有中间名首字母的姓氏。那么我们如何只获得姓氏并摆脱中间的首字母呢？简单！只需(Just)再次运行我们用来获取名称第二部分的相同公式。
所以我们只是在做另一项权利，这次将公式应用于组合的中间名首字母和姓氏单元格(name cell)。它将找到中间首字母之后的空格，然后将长度减去字符串末尾的空格字符数的位置。(space number)
所以你有它！您现在已经使用Excel(Excel)中的几个简单公式将名字和姓氏分成不同的列！显然，不是每个人都会以这种方式格式化他们的文本，但您可以轻松地对其进行编辑以满足您的需要。
文本到列
还有另一种简单的方法可以将组合文本分隔到Excel中的单独列中。这是一个名为Text to Columns的功能，效果很好。如果您有一列包含两条以上的数据，它的效率也会更高。
例如，下面我有一些数据，其中一行(one row)有 4 条数据，另一行有 5 条数据。我想分别将其分成 4 列和 5 列。如您所见，尝试使用上述公式是不切实际的。
在Excel 中(Excel)，首先选择要分隔的列。然后，继续并单击Data选项卡，然后单击Text to Columns。
这将打开 Text to Columns 向导(Columns wizard)。在第 1 步(step 1)中，您选择该字段是带分隔符的还是固定宽度的。在我们的例子中，我们将选择Delimited。
在下一个屏幕上，您将选择分隔符。您可以从制表符、分号、逗号、空格或键入自定义(custom one)选项中进行选择。
最后，您选择列的数据格式。通常，General对于大多数类型的数据都可以正常工作。如果您有特定的日期，例如日期，请选择该格式。
单击“完成(Finish)”并观察您的数据是如何神奇地分成列的。如您所见，一行变成了五列，另一行变成了四列。Text to Columns 功能(Columns feature)非常强大，可以让您的生活更轻松。
如果您在分隔名称时遇到的问题不是我上面的格式，请用您的数据发表评论，我会尽力提供帮助。享受！
How to Separate First and Last Names in Excel
If you use Excel a lot, you have probably run across a situatiоn where you have a name in a single cell and you need to separate the name into different cеlls. This is a very common issuе in Excel and you can probably do a Google search and download 100 different macros written by νarious people to do it for you.
However, in this post, I’ll show you how to setup a formula so you can do it yourself and actually understand what is going on. If you use Excel a lot, it’s probably a good idea to learn some of the more advanced functions so that you can do more interesting things with your data.
If you don’t like formulas and want a quicker solution, scroll down to the Text to Columns section, which teaches you how to use an Excel feature to do the same thing. In addition, the text to columns feature is also better to use if you have more than two items in a cell you need to separate. For example, if one column has 6 fields combined together, then using the formulas below will become really messy and complicated.
Separate Names in Excel
To get started, let’s see how names are usually stored in a Excel spreadsheet. The most common two ways I have seen are firstname lastname with just a space and lastname, firstname with a comma separating the two. Whenever I have seen a middle initial, it’s usually firstname midinitial lastname like below:
Using some simple formulas and combining a couple of them together, you can easily separate the first name, last name and middle initial into separate cells in Excel. Let’s start with extracting the first part of the name. In my case, we’re going to use two functions: left and search. Logically here’s what we need to do:
Search the text in the cell for a space or comma, find the position and then take out all the letters to the left of that position.
Here’s a simple formula that gets the job done correctly: =LEFT(NN, SEARCH(” “, NN) – 1), where NN is the cell that has the name stored in it. The -1 is there to remove the extra space or comma at the end of the string.
As you can see, we start out with the left function, which takes two arguments: the string and the number of characters you want to grab starting from the beginning of the string. In the first case, we search for a space by using double quotes and putting a space in-between. In the second case, we are looking for a comma instead of a space. So what is the result for the 3 scenarios I have mentioned?
We got the first name from row 3, the last name from row 5 and the first name from row 7. Great! So depending on how your data is stored, you have now extracted either the first name or the last name. Now for the next part. Here’s what we need to do logically now:
– Search the text in the cell for a space or comma, find the position and then subtract the position from total length of the string. Here’s what the formula would look like:
=RIGHT(NN,LEN(NN) -SEARCH(” “,NN))
So now we use the right function. This takes two arguments also: the string and the number of characters you want to grab starting from the end of the string going left. So we want the length of the string minus the position of the space or comma. That will give us everything to the right of the first space or comma.
Great, now we have the second part of the name! In the first two cases, you’re pretty much done, but if there is a middle initial in the name, you can see that the result still includes the last name with the middle initial. So how do we just get the last name and get rid of the middle initial? Easy! Just run the same formula again that we used to get the second section of the name.
So we are just doing another right and this time applying the formula on the combined middle initial and last name cell. It will find the space after the middle initial and then take the length minus the position of the space number of characters off the end of the string.
So there you have it! You have now split the first name and last name into separate columns using a few simple formulas in Excel! Obviously, not everyone will have their text formatted in this way, but you can easily edit it to suit your needs.
Text to Columns
There is also another easy way you can separate combined text into separate columns in Excel. It’s a featured called Text to Columns and it works very well. It’s also much more efficient if you have a column that has more than two pieces of data.
For example, below I have some data where one row has 4 pieces of data and the other row has 5 pieces of data. I would like to split that into 4 columns and 5 columns, respectively. As you can see, trying to use the formulas above would be impractical.
In Excel, first select the column you want to separate. Then, go ahead and click on the Data tab and then click on Text to Columns.
This will bring up the Text to Columns wizard. In step 1, you choose whether the field is delimited or fixed width. In our case, we’ll choose Delimited.
On the next screen, you will choose the delimiter. You can pick from tab, semicolon, comma, space or type a custom one in.
Finally, you choose the data format for the column. Normally, General will work just fine for most types of data. If you have something specific like dates, then choose that format.
Click Finish and watch how your data is magically separated into columns. As you can see, one row turned into five columns and the other one into four columns. The Text to Columns feature is very powerful and can make your life a lot easier.
If you are having problems separating names not in the format I have above, post a comment with your data and I’ll try to help. Enjoy!
