How to concatenate columns in Excel

  • Thread starter Thread starter M_1
  • Start date Start date
  • Tags Tags
    Columns Excel
Click For Summary
SUMMARY

To concatenate two columns in Excel without specifying the exact number of non-empty cells, users can utilize VBA macros. The method involves using the Range("A1").End(xlDown) function to identify the last occupied cell in column A. This allows for dynamic concatenation of columns A and B into column C, accommodating varying lengths of data in both columns. The official Microsoft support page provides additional guidance on combining text from multiple cells.

PREREQUISITES
  • Basic understanding of Excel functions and formulas
  • Familiarity with VBA (Visual Basic for Applications)
  • Knowledge of Excel's Range object and its methods
  • Experience with navigating the Excel interface
NEXT STEPS
  • Learn how to create and run VBA macros in Excel
  • Explore the use of the Range object in VBA for dynamic data manipulation
  • Study Excel's CONCATENATE function and its modern alternatives like TEXTJOIN
  • Investigate error handling in VBA to manage empty cells effectively
USEFUL FOR

Excel users, data analysts, and anyone looking to automate data manipulation tasks in spreadsheets will benefit from this discussion.

M_1
Messages
30
Reaction score
1
Say I have two columns containing data in Excel. Column A contains 10 non empty cell and column B contains 12 non empty cells. How can I create a column C with 10 + 12 cells without specifically telling Excel to put cell B1 in cell C11. In other words I would like to concatenate column A and B into column C without knowing the number of non empty cells in A or B.
 
Technology news on Phys.org
I don't think you can in plain Excel. You can with macros, though. Range("A1").End(xlDown) will return the last occupied cell below A1 (End acts like control and the down arrow key, so careful that A2 isn't empty). You can get the row from there.
 

Similar threads

Python Partial pivoting in getting the reduced row echelon form of a matrix
  • · Replies 2 ·
Replies
2
Views
2K
MHB How can I get equal columns and rows from a total?
  • · Replies 1 ·
Replies
1
Views
1K
Excel Macros & VBA | Learn Programming Basics
  • · Replies 18 ·
Replies
18
Views
6K
Python Elementary Python Questions: Data Frames, k-nary functions
  • · Replies 8 ·
Replies
8
Views
2K
Name for "Concatenation Operator"(Rel. Algebra, SQL)
  • · Replies 16 ·
Replies
16
Views
4K
Fortran Output of max consecutive values/days
  • · Replies 4 ·
Replies
4
Views
2K
Unexpected behavior in Excel when sorting
  • · Replies 9 ·
Replies
9
Views
3K
How to have "Excel for Mac 2016" Auto-convert entry of yymmdd to yyyy-mm-dd
  • · Replies 9 ·
Replies
9
Views
2K
Python Need tips on how to properly organize tkinter script
  • · Replies 4 ·
Replies
4
Views
2K
Python Query runs fine in MySQL, but returns empty list in Python
  • · Replies 7 ·
Replies
7
Views
5K