英文:
Need a macro for Concate two columns
问题
I wants to merge Col "Name" with Col "Lastname" based on their header name & create a new column "Name" paste their that merged values & delete old "Name" column
需要如此操作：
英文:
I wants to merge Col "Name" with Col "Lastname" based on their header name & create a new column "Name" paste their that merged values & delete old "Name" column
Need like this
答案1
得分: 1
这是代码部分，不需要翻译：
- =TEXTJOIN(" ", TRUE, B2:C2)
- =B2 & " " & C2
无空格选项：
- =CONCAT(B3:C3)
另外还有Power Query的方法。如果你需要反复打开相同文件并进行相同操作，这是一个不错的方法。它还不会显示公式。选择数据的左上角单元格，然后按下Ctrl + t键，将数据转换为定义的表格。接下来点击数据 > 从表/范围获取。
Power Query编辑器将打开，选择转换 > 合并列，选择分隔符和新的列名OK。然后点击文件 > 关闭并加载。
还有其他使用Power Query的原因，比如你想要从CSV文件或网站中提取数据而不需要打开它。
英文:
There are different ways to do this. I would think you would want a space between names so I will use " " here to show the space. If you don't want that just use "".
Formula ways
- =TEXTJOIN(" ",TRUE,B2:C2)
- =B2 &" "& C2
no space option
- =CONCAT(B3:C3)
There is also the powerquery way. This is a good way if you have to open the same file to do the same thing time after and time again. It will also not show formulas. Select the top left cell of data and hit the keys Ctrl + t this turns your data into a defined table. Next click on Data > From Table/Range
The Power Query editor will open and you choose Tranform > Merge Columns choose the separator and the new Column name OK. Then click File > Close & Load.
There are other reasons to use power query such as you want to scrape data out of a csv files or website without opening it.
答案2
得分: 0
=LET(Header,A1:C1, data,A2:C4,
dataMerged,HSTACK(CHOOSECOLS(data,1) & CHOOSECOLS(data,2),DROP(data,,-1)),
VSTACK(Header,dataMerged))
英文:
If you have Excel 365 and want only one Formula to create the new table, you can use this formula:
=LET(Header,A1:C1, data,A2:C4,
dataMerged,HSTACK(CHOOSECOLS(data,1) & CHOOSECOLS(data,2),DROP(data,,-1)),
VSTACK(Header,dataMerged))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论