How to merge rows in a column into one cell in excel?

excel vba
red23jordan picture red23jordan · Nov 15, 2011 · Viewed 334.3k times · Source

E.g 

A1:I
A2:am
A3:a
A4:boy

I want to merge them all to a single cell "Iamaboy"
This example shows 4 cells merge into 1 cell however I have many cells (more than 100), I can't type them one by one using A1 & A2 & A3 & A4 what can I do?

Answer

Mischinab picture Mischinab · Nov 15, 2011

If you prefer to do this without VBA, you can try the following:

  1. Have your data in cells A1:A999 (or such)
  2. Set cell B1 to "=A1"
  3. Set cell B2 to "=B1&A2"
  4. Copy cell B2 all the way down to B999 (e.g. by copying B2, selecting cells B3:B99 and pasting)

Cell B999 will now contain the concatenated text string you are looking for.

Related questions

Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
How do I declare a global variable in VBA?

I wrote the following code: Function find_results_idle() Public iRaw As Integer Public iColumn As Integer iRaw = 1 iColumn = 1 And I get the error message: "invalid attribute in Sub or Function" Do you know what I did wrong? I tried …

Read More
How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

How can I use regular expressions in Excel and take advantage of Excel's powerful grid-like setup for data manipulation? In-cell function to return a matched pattern or replaced value in a string. Sub to loop through a column of data …

Read More