Good news, if you're spelling challenged -- or too lazy to type long words. You can combine cells in Excel, without CONCATENATE function. Keep reading, to learn the easy way to combine cells, and add some fancy formatting to the dates and numbers.
好消息,如果您在拼写方面遇到挑战-或懒得打长字。 您可以在Excel中合并单元格,而无需使用CONCATENATE函数。 继续阅读,学习组合单元格的简单方法,并为日期和数字添加一些精美的格式。
使用&运算子 (Use the & Operator)
Yes, instead of using CONCATENATE, you can use the ampersand operator -- & -- to combine cell values in Excel. That's a savings of 10 characters!
是的,您可以使用&运算符(&&)代替Excel合并单元格值,而不是使用CONCATENATE。 这样可以节省10个字符!
And in the hot weather we're having this summer, it's important to conserve your energy for more important things.
在今年夏天炎热的天气里,重要的是要节省能量。
Even more good news -- you don't need to remember how to spell "ampersand", and you can even use one of its less technical names -- "Fancy And" or "Shift-7".
甚至更多的好消息-您无需记住如何拼写“&”号,甚至可以使用其技术性较差的名称之一-“ Fancy And”或“ Shift-7”。
合并两个单元格 (Combine Two Cells)
In the cell where you'd like to see the combined values from two other cells:
在要查看其他两个单元格组合值的单元格中:
Type an = sign, to start the formula
键入=符号以启动公式
- Click on the first cell that you want to combine 单击要合并的第一个单元格
Type an &
键入&
- Click on the second cell that you want to combine. 单击要合并的第二个单元格。
In the screenshot below, the product name and amount are being combined, and the formula is:
在下面的屏幕截图中,产品名称和数量组合在一起,公式为:
=B2&E2
= B2&E2
![concatenate01 concatenate01](https://i-blog.csdnimg.cn/blog_migrate/9b5091b188f97a90178d93bfb2f22fad.png)
- Press Enter, to complete the formula 按Enter键以完成公式
The values from the two cells are combined into one continuous text string, showing the product name and price.
来自两个单元格的值合并为一个连续的文本字符串,显示产品名称和价格。
![concatenate02 concatenate02](https://i-blog.csdnimg.cn/blog_migrate/9463c2325e62ab26c77fafe76c326a11.png)
在组合文字之间添加空格 (Add a Space Between Combined Text)
Your formula to combine the product name and price cells worked as advertised, but the results would look better with a space between the product name and price. To create a space, you can include a text string in the formula.
您将产品名称和价格单元格结合起来的公式可以像宣传的那样工作,但是在产品名称和价格之间留一个空格,结果看起来会更好。 要创建空间,可以在公式中包含文本字符串。
- Select the cell with the formula 选择具有公式的单元格
- Click after the first cell reference 在第一个单元格引用之后单击
Type the & operator
键入&运算符
- Type " " (double quote, space, double quote) 输入“”(双引号,空格,双引号)
Type the & operator
键入&运算符
- Press Enter to complete the revision 按Enter键完成修订
The revised formula is:
修改后的公式为:
=B2&" "&E2
= B2&“”&E2
The product name and price now have a space between them.
现在,产品名称和价格之间有一个空格。
![concatenate03 concatenate03](https://i-blog.csdnimg.cn/blog_migrate/5024445ca7edeecd0bc4769ab4445b8a.png)
组合单元格中的数字格式 (Format Numbers in Combined Cells)
When you combine text with a date or number, you can format the result by using the TEXT function. The TEXT function has two arguments -- the cell reference, and the formatting. In this example, you can format the number as currency, with two decimal places.
将文本与日期或数字组合时,可以使用TEXT函数设置结果的格式。 TEXT函数有两个参数-单元格引用和格式。 在此示例中,您可以将数字格式设置为货币,并保留两位小数。
TEXT(E2,"$#,##0.00")
TEXT(E2,“ $#,## 0.00”)
- Select the cell with the formula 选择具有公式的单元格
- Change the second cell reference, to include the TEXT function 更改第二个单元格引用,以包含TEXT函数
- Press Enter to complete the revision 按Enter键完成修订
The revised formula is:
修改后的公式为:
=B2&" "&TEXT(E2,"$#,##0.00")
= B2&“”&TEXT(E2,“ $#,## 0.00”)
The product name and price now have a space between them, and currency formatting on the number.
现在,产品名称和价格之间有一个空格,数字上的货币格式也是如此。
![concatenate04 concatenate04](https://i-blog.csdnimg.cn/blog_migrate/b5b24a5b7f16d69c3816f206d00ddb53.png)
有关数字格式的帮助 (Help With Number Formats)
If you need help with setting up the Number Format argument in the TEXT function, there are a few more examples on the Combine Cells in Excel page.
如果需要有关在TEXT函数中设置数字格式参数的帮助,请在Excel中的“ 合并单元格”页面上找到更多示例。
You can also format a sample cell in Excel, using the Number Format commands. Then, to see its formatting code:
您还可以使用数字格式命令在Excel中格式化示例单元格。 然后,查看其格式代码:
- Select the formatted cell 选择格式化的单元格
- Press Ctrl+1, to open the Format Cells dialog box. 按Ctrl + 1,打开“设置单元格格式”对话框。
- On the Numbers tab, click the Custom category 在数字选项卡上,单击自定义类别
- Copy the formatting from the Type box. 从“类型”框中复制格式。
- Close the dialog box, and paste the formatting into the TEXT formula 关闭对话框,然后将格式粘贴到TEXT公式中
![concatenate05 concatenate05](https://i-blog.csdnimg.cn/blog_migrate/2624caf93a3d51221e6f269176ae455d.png)
视频:在Excel中合并单元格而无需串联 (Video: Combine Cells in Excel Without Concatenate)
To see the steps to combine cells in Excel without CONCATENATE, watch this short Excel video.
要查看在没有CONCATENATE的情况下在Excel中合并单元格的步骤,请观看这段简短的Excel视频。
翻译自: https://contexturesblog.com/archives/2010/07/30/combine-cells-in-excel-without-concatenate/