vba 有没有一种简单的方法可以在 Excel 中不用 CONCATENATE 将列转换为文本？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19192621/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there an easy way to turn columns-to-text without CONCATENATE in Excel?
提问by Bob Hopez
I'm aware of the text-to-columns button. Can the opposite be done by selecting columns, and possibly running a macro or using a similar button?
我知道文本到列按钮。是否可以通过选择列并可能运行宏或使用类似按钮来完成相反的操作？
回答by David Zemens
Normally I say "Please post your code" for help/assistance writing macros, but this one is really, really simple.
通常我会说“请发布您的代码”以获得编写宏的帮助/帮助，但这真的非常简单。
Public Function ColumnsToText(rng As Range, Optional DelimitBy As String = " ") As String
'Applies on a row/partial row of data. Must be continuous cells, e.g., A1:D1.
Dim var As Variant
var = Application.Transpose(Application.Transpose(rng.Value))
ColumnsToText = Join(var, DelimitBy)
End Function
ENter it in a worksheet cell like:
在工作表单元格中输入它，例如：
=ColumnsToText(A1:F1)
=ColumnsToText(A1:F1)
Or you can specify an optional delimiter, it is a space by default, but you could use any string characters:
或者您可以指定一个可选的分隔符，默认情况下它是一个空格，但您可以使用任何字符串字符：
=ColumnsToText(A1:F1,"%")
=ColumnsToText(A1:F1,"%")
Etc.
等等。
回答by D_Bester
I was intrigued by the answer @David Zemens gave using a double transpose. His answer works well. I did found one other way to get a single row into a 1 dimensional array. Also I wanted a function that could handle a column. So here is my alternate answer.
我对@David Zemens 使用双转置给出的答案很感兴趣。他的回答很有效。我确实找到了另一种将单行放入一维数组的方法。另外我想要一个可以处理一列的函数。所以这是我的替代答案。
Public Function ColumnOrRowToText(rng As Range, Optional DelimitBy As String = " ") As String
'rng must be a single row or a single column; cannot handle multiple columns or rows
Dim var As Variant
If rng.Rows.Count = 1 Then
var = Application.WorksheetFunction.Index(rng.Value, 1, 0) 'for a single row
Else
var = Application.Transpose(rng.Value) 'for a single column
End If
'var must be a 1 dimensional array
ColumnOrRowToText = Join(var, DelimitBy)
End Function
回答by Warren
I believe the true reverse of Text-to-Columns is the formula TEXTJOIN. It will return your selected column entries into one cell in one long string separated by a delimeter that you can set on your own.
我相信 Text-to-Columns 的真正反面是 TEXTJOIN 公式。它会将您选择的列条目返回到一个长字符串中的一个单元格中，该字符串由您可以自己设置的分隔符分隔。
回答by Ryan Chase
If instead what you're interested in is "unpivoting" your column to be values in their own column, then essentially you need to create a flat file.
相反，如果您感兴趣的是将您的列“反透视”为它们自己列中的值，那么基本上您需要创建一个平面文件。
This is best accomplished with R' "gather" function or Python Pandas' "melt" function. It can also be accomplished through macros in Excel. If you don't know macros, a more rudimentary approach is to:
这最好使用 R 的“收集”函数或 Python Pandas 的“融化”函数来完成。也可以通过 Excel 中的宏来完成。如果你不知道宏，一个更基本的方法是：
- create a new sheet and paste all the values of your first column (assuming this is a column you want to keep) as the first column in the new worksheet (column A). Now, in column B, paste the name of the ***first column header that you want to be value in a column. For example, below you want the column header 'red' to be in it's own column - maybe a "color" column. Drag 'red' down column B for every row where you have a unique record in column A.
- Then, underneath this- paste all the values in column A again, while pasting in column B the secondcolumn that you want to perform "columns to text" on ('orange' in the below example).
- Repeat for however many columns that you need to list as "text".
- Finally, in column C, use the values that you have in column A and column B and create an index(match(match()) to find out what value is at the intersection of the two values you have in column A and column B.
- 创建一个新工作表并将第一列的所有值（假设这是您要保留的列）粘贴为新工作表（A 列）中的第一列。现在，在 B 列中，粘贴要作为列中值的 ***第一个列标题的名称。例如，下面您希望列标题“红色”位于它自己的列中 - 可能是“颜色”列。对于在 A 列中具有唯一记录的每一行，将“红色”向下拖动到 B 列。
- 然后，在此下方 - 再次粘贴 A 列中的所有值，同时在 B列中粘贴要对其执行“列到文本”的第二列（以下示例中的“橙色”）。
- 对您需要列为“文本”的多列重复此操作。
- 最后，在 C 列中，使用 A 列和 B 列中的值并创建索引（match(match()) 以找出 A 列和 B 列中的两个值的交集处的值.
Here's an example:
下面是一个例子：
回答by MSWJayne
Excel help offers this technique:
Excel 帮助提供了这种技术：
Given you are combining cell A1 and B1
In the new cell, enter
=(A1&" "&B1){{Note the space between the apostrophes}}
鉴于您正在组合单元格 A1 和 B1
在新单元格中，输入
=(A1&" "&B1){{注意撇号之间的空格}}
I used this to insert filler text as well, so mine read =(P2&", also"&Q2).
我也用它来插入填充文本，所以我读了=(P2&", also"&Q2).
