Excel - 连接许多列 [英] Excel - Concatenate many columns

问题描述

我想在Excel中连接一堆列。我知道我可以手动执行：

= A1&，& B1&，& C1 （等等）

但我有大约40列，我正在寻找一种简化这个过程的方法。

提前感谢任何帮助！

解决方案

作为使用范围

 公共功能ClarkeyCat（ByRef rng As Range）As Variant 
 
 Dim c As Range 
 Dim ans As Variant 
 
对于每个c在rng 
 
如果（c.Value<>）然后
 ans = IIf（ans =，，ans&，）& c.Value 
 End If 
 
下一个
 
 ClarkeyCat = ans 
结束函数
  

更改 Variant 类型，如果需要（至 string ，很可能）。

使用如下：

I'm trying to concatenate a bunch of columns in Excel. I know I can manually do with:

=A1&", "&B1&", "&C1 (and so on)

but I have about 40 columns, and I'm looking for a way to streamline this process.

Thanks in advance for any help!

解决方案

As a user function taking a range

Public Function ClarkeyCat(ByRef rng As Range) As Variant

Dim c As Range
Dim ans As Variant

For Each c In rng

If (c.Value <> "") Then
    ans = IIf(ans = "", "", ans & ",") & c.Value
End If

Next

ClarkeyCat = ans
End Function

Changing the Variant types, if you need to (to string, most likely).

Use like this:

这篇关于Excel - 连接许多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！