如何在Excel中将多个单元格中的文本合并到一个单元格中
If you have a large worksheet in an Excel workbook in which you need to combine text from multiple cells, you can breathe a sigh of relief because you don’t have to retype all that text. You can easily concatenate the text.
如果您在Excel工作簿中有一个较大的工作表,需要在其中合并多个单元格中的文本,则可以放心,因为您不必重新输入所有文本。 您可以轻松地串联文本。
Concatenate is simply a fancy way ot saying “to combine” or “to join together” and there is a special CONCATENATE function in Excel to do this. This function allows you to combine text from different cells into one cell. For example, we have a worksheet containing names and contact information. We want to combine the Last Name and First Name columns in each row into the Full Name column.
串联只是说“组合”或“连接在一起”的一种奇特方式,并且Excel中有一个特殊的CONCATENATE函数来执行此操作。 此功能使您可以将来自不同单元格的文本合并为一个单元格。 例如,我们有一个包含名称和联系信息的工作表。 我们要将每行的“姓氏”和“名字”列合并为“全名”列。
To begin, select the first cell that will contain the combined, or concatenated, text. Start typing the function into the cell, starting with an equals sign, as follows.
首先,选择第一个包含合并的或串联的文本的单元格。 如下所示,以等号开始在单元格中键入该函数。
=CONCATENATE(
Now, we enter the arguments for the CONCATENATE function, which tell the function which cells to combine. We want to combine the first two columns, with the First Name (column B) first and then the Last Name (column A). So, our two arguments for the function will be B2 and A2.
现在,我们输入CONCATENATE函数的参数,该参数告诉该函数合并哪些单元格。 我们要合并前两列,首先是名字(B列),然后是姓氏(A列)。 因此,该函数的两个参数将为B2和A2。
There are two ways you can enter the arguments. First, you can type the cell references, separated by commas, after the opening parenthesis and then add a closing parenthesis at the end:
您可以通过两种方式输入参数。 首先,您可以在开括号后键入单元格引用(以逗号分隔),然后在末尾添加一个闭括号:
=CONCATENATE(B2,A2)
You can also click on a cell to enter it into the CONCATENATE function. In our example, after typing the name of the function and the opening parenthesis, we click on the B2 cell, type a comma after B2 in the function, click on the A2 cell, and then type the closing parenthesis after A2 in the function.
您也可以单击一个单元格以将其输入到CONCATENATE函数中。 在我们的示例中,键入函数的名称和左括号后,我们单击B2单元格,在函数B2后面键入逗号,单击A2单元格,然后在函数中A2后面键入右括号。
Press Enter when you’re done adding the cell references to the function.
完成将单元格引用添加到函数后,按Enter键。
Notice that there is no space in between the first and last name. That’s because the CONCATENATE function combines exactly what’s in the arguments you give it and nothing more. There is no space after the first name in B2, so no space was added. If you want to add a space, or any other punctuation or details, you must tell the CONCATENATE function to include it.
请注意,名字和姓氏之间没有空格。 这是因为CONCATENATE函数完全结合了您提供给它的参数中的内容,仅此而已。 B2中名字后面没有空格,因此没有添加空格。 如果要添加空格或任何其他标点符号或详细信息,则必须告诉CONCATENATE函数包括它。
To add a space between the first and last names, we add a space as another argument to the function, in between the cell references. To do this, we type a space surrounded by double quotes. Make sure the three arguments are separated by commas.
为了在名字和姓氏之间添加一个空格,我们在单元格引用之间添加一个空格作为函数的另一个参数。 为此,我们键入一个用双引号引起来的空格。 确保三个参数用逗号分隔。
=CONCATENATE(B2," ",A2)
Press Enter.
按Enter键。
That’s better. Now, there is a space between the first and last names.
这样更好现在,名字和姓氏之间有一个空格。
Now, you’re probably thinking you have to type that function in every cell in the column or manually copy it to each cell in the column. Actually, you don’t. We’ve got another neat trick that will help you quickly copy the CONCATENATE function to the other cells in the column (or row). Select the cell in which you just entered the CONCATENATE function. The small square on the lower-right corner of the selected is called the fill handle. The fill handle allows you to quickly copy and paste content to adjacent cells in the same row or column.
现在,您可能会想必须在该列的每个单元格中键入该函数,或将其手动复制到该列的每个单元格中。 实际上,您没有。 我们还有另一个巧妙的技巧,可以帮助您快速将CONCATENATE函数复制到列(或行)中的其他单元格上。 选择您刚刚在其中输入CONCATENATE函数的单元格。 所选右下角的小方块称为填充手柄。 填充手柄使您可以快速将内容复制并粘贴到同一行或同一列中的相邻单元格中。
Move your cursor over the fill handle until it turns into a black plus sign and then click and drag it down.
将光标移到填充手柄上,直到变成黑色加号,然后单击并向下拖动。
The function you just entered is copied down to the rest of the cells in that column, and the cell references are changed to match the row number for each row.
您刚刚输入的函数将被复制到该列的其余单元格中,并且单元格引用将更改为与每一行的行号匹配。
You can also concatenate text from multiple cells using the ampersand (&) operator. For example, you can enter =B2&" "&A2
to get the same result as =CONCATENATE(B2,” “,A2)
. There’s no real advantage of using one over the other. although using the ampersand operator results in a shorter entry. However, the CONCATENATE function may be more readable, making it easier to understand what’s happening in the cell.
您还可以使用&运算符将多个单元格中的文本连接起来。 例如,您可以输入=B2&" "&A2
获得与=CONCATENATE(B2,” “,A2)
相同的结果。 使用一个相对于另一个没有真正的优势。 尽管使用&运算符可缩短输入时间。 但是,CONCATENATE函数可能更具可读性,从而更容易理解单元格中发生的事情。
翻译自: https://www.howtogeek.com/279719/how-to-combine-text-from-multiple-cells-into-one-cell-in-excel/
原文地址: https://blog.csdn.net/cuma1988/article/details/109042495
本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系博主进行删除。
相关文章