使用& [英] Joining a range of cells in excel using &
问题描述
&
连接一组单元格,如下所示: A1& B1& C1& D1
。有没有办法写一个范围 A1:P1
使用&
?
您可以使用一系列列中的公式(从上一个顺序连接)或 UDF (
公式方法
F2是,
= A2
G2中的公式是
= IF(LEN(B2),F2&,& B2,F2)
填充G2,以捕获所有可用的文本值。
VBA UDF方法
点击 Alt + F11 ,当VBE打开时,立即使用下拉菜单插入►模块( Alt + I , M )。将以下内容粘贴到名为类似 Book1 - Module1(Code)的新窗格中。
功能udf_stitch_Together (r As Range,可选d As String =,)As String
Dim s As String,c As Range
对于每个c在r
中如果CBool(Len(c.Text) )然后_
s = s& IIf(Len(s),d,vbNullString)& c.Text
下一步c
udf_stitch_Together = s
结束功能
点击 Alt + Q 返回到您的工作表。
语法简单,
udf_stitch_Together(<连接范围>,< [可选]分隔符>)
为了您的目的,这将是
= udf_stitch_Together(A2:P2)
...或者
= udf_stitch_Together(A2:P2, ,)
根据需要填写。请注意,我在F5:F6中使用了 = udf_stitch_Together(A5:P5,;)
来演示更改分隔符的能力。
I know I can concatenate a set of cells using &
like this:
A1 & B1 & C1 & D1
. Is there a way to write this for a range A1:P1
using &
?
You can accomplish this with a formula in a series of columns (each sequentially concatenating from the previous) or with a UDF (User Defined Function).
Formula Method
The formula in F2 is,
=A2
The formula in G2 is,
=IF(LEN(B2), F2&","&B2, F2)
Fill G2 right to catch all of the available text values.
VBA UDF Method
Tap Alt+F11 and when the VBE opens, immediately use the pull-down menus to Insert ► Module (Alt+I,M). Paste the following into the new pane titled something like Book1 - Module1 (Code).
Function udf_stitch_Together(r As Range, Optional d As String = ", ") As String
Dim s As String, c As Range
For Each c In r
If CBool(Len(c.Text)) Then _
s = s & IIf(Len(s), d, vbNullString) & c.Text
Next c
udf_stitch_Together = s
End Function
Tap Alt+Q to return to your worksheet.
The syntax is simply,
udf_stitch_Together(<range to concatenate>, <[optional] delimiter>)
For your purposes this would be,
=udf_stitch_Together(A2:P2)
... or,
=udf_stitch_Together(A2:P2, ", ")
Fill down as necessary. Note that I have used =udf_stitch_Together(A5:P5, ";")
in F5:F6 to demonstrate the ability to change the delimiter.
这篇关于使用&的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!