Hi I'm not even sure if this is actually possible in excel, but I am trying to merge two rows together, for example I have data that looks a little like this (each like would be a separate row):
嗨,我甚至不确定这在excel中是否真的可行,但我试图将两行合并在一起,例如我的数据看起来有点像这样(每个都像是一个单独的行):
1001001
0100101
1010101
1010101
and I would like to merge the two rows like:
我想合并两行,如:
1001001-0100101
1010101-1010101
Does anyone have any suggestions? Thank you so much
有没有人有什么建议?非常感谢
7 个解决方案
#1
7
String concatenation in excel uses &
excel中的字符串连接使用&
=(A1 & "-" & A2)
#2
2
The advice regarding strong concatenation is exactly the functionality I would suggest adding to a VBA code. Functional call, and then parameters referring to cells or variables rocks my world because I can fill handle the rest! :D Thanks 1E+6 :D
关于强级联的建议正是我建议添加到VBA代码的功能。函数调用,然后参考细胞或变量的参数摇滚我的世界,因为我可以填补其余的! :D谢谢1E + 6:D
#3
1
To the immediate right of the end of your last column, at the first row, type the following:
在最后一列末尾的最右边,在第一行,键入以下内容:
=A1 & "-" & B1
Go one column over to the right and type this:
向右移一列,然后输入:
=A2 & "-" & B2
Now highlight both of those new cells that you just typed, and grab the little autofill box in the lower right corner of your selected cells, and drag over until your new area is the same width as the original row.
现在突出显示刚刚键入的两个新单元格,并抓住所选单元格右下角的小自动填充框,然后拖动直到新区域与原始行的宽度相同。
Now, highlight the entire new section you've just created, and drag it down to the bottom of your data area.
现在,突出显示刚刚创建的整个新部分,并将其向下拖动到数据区域的底部。
Highlight the whole block of new data, hit Ctrl-C, and then right click somwhere and choose "Paste Special". In the dialog that appears, choose "Values", and hit "OK". Now you can move this new block anywhere you want without regard to other cells.
突出显示整个新数据块,按Ctrl-C,然后右键单击somwhere并选择“选择性粘贴”。在出现的对话框中,选择“值”,然后单击“确定”。现在,您可以将此新块移动到任何您想要的位置而不必考虑其他单元格。
Finally, you will need to delete every other row of your newly pasted data, beginning with the second row.
最后,您需要从第二行开始删除新粘贴数据的每隔一行。
#4
1
Excel has a CONCATENATE function to join multiple cell values and adhoc text. Up to 255 values can be joined this way
Excel具有CONCATENATE函数来连接多个单元格值和即时文本。通过这种方式最多可以加入255个值
=CONCATENATE(A1,"-",A2)
This would return the following based on your data:
这将根据您的数据返回以下内容:
1001001-0100101
1010101-1010101
#5
0
Copy the entire data and paste it in duplicate to the right of the original data.
复制整个数据并将其粘贴到原始数据的右侧。
1001001 1001001
0100101 0100101
1010101 1010101
1010101 1010101
Delete a few cells at the top of the new columns -- one row's worth. You have now gotten each record consolidated on one row. You also have a junk record for every other row.
删除新列顶部的几个单元格 - 一行值。您现在已经将每条记录整合在一行中。每隔一行你也有一个垃圾记录。
1001001 0100101
0100101 1010101 (junk row)
0100101 1010101(垃圾行)
1010101 1010101
1010101 (junk row)
1010101(垃圾行)
Find a way to delete the junk records. I might start by adding a column that is consecutive numbers ("Fill Series"), to restore the sort order later. Then find a way to sort for odd-numbers then even-numbers, such as a column with formula IF(divisible by 2,"odd","even"). Then delete half the records.
找到一种删除垃圾记录的方法。我可能会首先添加一个连续数字列(“填充系列”),以便稍后恢复排序顺序。然后找到一种方法来对奇数进行排序,然后对偶数进行排序,例如具有公式IF的列(可被2整除,“奇数”,“偶数”)。然后删除一半的记录。
#6
0
Just enter the letters and then ALT+ENTER from werer you have to type in the second cell
只需输入字母,然后输入ALT + ENTER,你必须输入第二个单元格
#7
0
Assuming your data starts in cell A1, two options to not have to delete unwanted lines.
假设您的数据在单元格A1中开始,两个选项不必删除不需要的行。
-
if you have an empty line between each group of values (as you seem to have): use the below formula, and copy it down. It will return the right results until there are no more values, at which point it will return "-"
如果每组值之间都有一个空行(如您所示):使用下面的公式,并将其复制下来。它将返回正确的结果,直到没有更多的值,此时它将返回“ - ”
=INDEX(A:A,1+3*(ROW()-1))&"-"&INDEX(A:A,2+3*(ROW()-1)) -
if you don't have an empty line, same principle, slightly different formula:
如果你没有空行,同样的原则,略有不同的公式:
=INDEX(A:A,1+2*(ROW()-1))&"-"&INDEX(A:A,2+3*(ROW()-1))
When you're done, copy / paste values and you can delete column A to just have the concatenated results available.
完成后,复制/粘贴值,您可以删除列A以使连接结果可用。
#1
7
String concatenation in excel uses &
excel中的字符串连接使用&
=(A1 & "-" & A2)
#2
2
The advice regarding strong concatenation is exactly the functionality I would suggest adding to a VBA code. Functional call, and then parameters referring to cells or variables rocks my world because I can fill handle the rest! :D Thanks 1E+6 :D
关于强级联的建议正是我建议添加到VBA代码的功能。函数调用,然后参考细胞或变量的参数摇滚我的世界,因为我可以填补其余的! :D谢谢1E + 6:D
#3
1
To the immediate right of the end of your last column, at the first row, type the following:
在最后一列末尾的最右边,在第一行,键入以下内容:
=A1 & "-" & B1
Go one column over to the right and type this:
向右移一列,然后输入:
=A2 & "-" & B2
Now highlight both of those new cells that you just typed, and grab the little autofill box in the lower right corner of your selected cells, and drag over until your new area is the same width as the original row.
现在突出显示刚刚键入的两个新单元格,并抓住所选单元格右下角的小自动填充框,然后拖动直到新区域与原始行的宽度相同。
Now, highlight the entire new section you've just created, and drag it down to the bottom of your data area.
现在,突出显示刚刚创建的整个新部分,并将其向下拖动到数据区域的底部。
Highlight the whole block of new data, hit Ctrl-C, and then right click somwhere and choose "Paste Special". In the dialog that appears, choose "Values", and hit "OK". Now you can move this new block anywhere you want without regard to other cells.
突出显示整个新数据块,按Ctrl-C,然后右键单击somwhere并选择“选择性粘贴”。在出现的对话框中,选择“值”,然后单击“确定”。现在,您可以将此新块移动到任何您想要的位置而不必考虑其他单元格。
Finally, you will need to delete every other row of your newly pasted data, beginning with the second row.
最后,您需要从第二行开始删除新粘贴数据的每隔一行。
#4
1
Excel has a CONCATENATE function to join multiple cell values and adhoc text. Up to 255 values can be joined this way
Excel具有CONCATENATE函数来连接多个单元格值和即时文本。通过这种方式最多可以加入255个值
=CONCATENATE(A1,"-",A2)
This would return the following based on your data:
这将根据您的数据返回以下内容:
1001001-0100101
1010101-1010101
#5
0
Copy the entire data and paste it in duplicate to the right of the original data.
复制整个数据并将其粘贴到原始数据的右侧。
1001001 1001001
0100101 0100101
1010101 1010101
1010101 1010101
Delete a few cells at the top of the new columns -- one row's worth. You have now gotten each record consolidated on one row. You also have a junk record for every other row.
删除新列顶部的几个单元格 - 一行值。您现在已经将每条记录整合在一行中。每隔一行你也有一个垃圾记录。
1001001 0100101
0100101 1010101 (junk row)
0100101 1010101(垃圾行)
1010101 1010101
1010101 (junk row)
1010101(垃圾行)
Find a way to delete the junk records. I might start by adding a column that is consecutive numbers ("Fill Series"), to restore the sort order later. Then find a way to sort for odd-numbers then even-numbers, such as a column with formula IF(divisible by 2,"odd","even"). Then delete half the records.
找到一种删除垃圾记录的方法。我可能会首先添加一个连续数字列(“填充系列”),以便稍后恢复排序顺序。然后找到一种方法来对奇数进行排序,然后对偶数进行排序,例如具有公式IF的列(可被2整除,“奇数”,“偶数”)。然后删除一半的记录。
#6
0
Just enter the letters and then ALT+ENTER from werer you have to type in the second cell
只需输入字母,然后输入ALT + ENTER,你必须输入第二个单元格
#7
0
Assuming your data starts in cell A1, two options to not have to delete unwanted lines.
假设您的数据在单元格A1中开始,两个选项不必删除不需要的行。
-
if you have an empty line between each group of values (as you seem to have): use the below formula, and copy it down. It will return the right results until there are no more values, at which point it will return "-"
如果每组值之间都有一个空行(如您所示):使用下面的公式,并将其复制下来。它将返回正确的结果,直到没有更多的值,此时它将返回“ - ”
=INDEX(A:A,1+3*(ROW()-1))&"-"&INDEX(A:A,2+3*(ROW()-1)) -
if you don't have an empty line, same principle, slightly different formula:
如果你没有空行,同样的原则,略有不同的公式:
=INDEX(A:A,1+2*(ROW()-1))&"-"&INDEX(A:A,2+3*(ROW()-1))
When you're done, copy / paste values and you can delete column A to just have the concatenated results available.
完成后,复制/粘贴值,您可以删除列A以使连接结果可用。