Combing multiple rows of data from one column into one cell

telewats

New Member
Joined
Jan 9, 2006
Messages
44
Hello everyone,

I'm attempting to combine multiple cells of data (all from one column) into a single cell. The thing is, I have over 1k rows, so it's not feasible to use the Contatenate formula. I've done this before, in a simple macro, I believe, but I can't figure it out this time. Here's an example of what I'm trying to contatenate, assuming these are all in, say, Column A, Rows 1,2,3,etc:

319<cr>550<cr>IAIAET<cr>
319<cr>553<cr>IAIAET<cr>
507<cr>287<cr>MNMNET<cr>
507<cr>289<cr>MNMNET<cr>
515<cr>419<cr>IAIAET<cr>

I'd like combine all the data into Column B, Row 1, so that it would look like this:
319<cr>550<cr>IAIAET<cr>319<cr>553<cr>IAIAET<cr>507<cr>287<cr>MNMNET<cr>507<cr>289<cr>MNMNET<cr>515<cr>419<cr>IAIAET<cr>

Does anyone have any ideas? And, thanks, in advance, for the help!

Oh, and I'm not sure why, (and this probably doesn't matter for the purpose of this thread) but the data I posted in is not displaying correctly when I submitted the thread. Each bit of data, 319, 550, IAIAET, etc. has a carriage return (cr) between them, and each "cr" is surrounded by <>.
 
Last edited:

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

jmthompson

Well-known Member
Joined
Mar 31, 2008
Messages
966
so do you want the values in A from all 100 rows in the same cell?

If not, what determines which rows are combined and where you want them combined?
 

telewats

New Member
Joined
Jan 9, 2006
Messages
44
Unfortunately, no, I can't have all cells in the row concatenated at once. I'm using the combined data as a macro for another software program and it only allows 20 lines of data to be entered at once.
 

RSXchin

Well-known Member
Joined
Oct 23, 2010
Messages
758
try this and let me know how it works for you. This puts everything from column A into cell A1

Code:
Sub all_of_a()
    HugeText = Nothing
    Cells.Replace what:=Chr(10), replacement:="", lookat:= _
        xlPart, searchorder:=xlByRows, MatchCase:=False, searchformat:=False, _
        ReplaceFormat:=False
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        HugeText = HugeText & Trim(Range("a" & i))
    Next i
    Range("a1") = HugeText
End Sub
 

telewats

New Member
Joined
Jan 9, 2006
Messages
44

ADVERTISEMENT

Thank you for the suggestion. Unfortunately, I'm not able to get it to work. I'm getting an error that the named argument isn't found. Maybe I'm not doing something correctly?
Also, though, if possible, instead of moving the entire column at once, I'd rather be able to highlight the cells (20 at a time) that I want to concatenate and then run the macro on just those... is that possible?
 

RSXchin

Well-known Member
Joined
Oct 23, 2010
Messages
758
this is for the entire column. Fixed that. can't think of how to do just a selected range
Code:
Sub all_of_a()
    HugeText = ""
    Cells.Replace what:=Chr(10), replacement:="", lookat:= _
        xlPart, searchorder:=xlByRows, MatchCase:=False, searchformat:=False, _
        ReplaceFormat:=False
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        HugeText = HugeText & Trim(Range("a" & i))
    Next i
    Range("a1") = HugeText
End Sub
 

irwina

New Member
Joined
Apr 28, 2010
Messages
24
Hi all,

This is a great solution, however wanted to ask assistance to concatenate based on a range.

I have a list of page numbers with several values for each. I wanted to combine all values for that page in a single cell with a separator.
Value (1-5), Page (1-2) and desired result (1, 2) is shown below:
1 1 1, 2
2 1 1, 2
3 2 3, 4, 5
4 2 3, 4, 5
5 2 3, 4, 5
etc for around 1,000 rows

I will then remove any duplicates in the next part of the macro to obtain a single row for each page.

Apologies, I cannot format this correctly.

Does anyone know how this can be done?

Thanks in advance for your help.
 

irwina

New Member
Joined
Apr 28, 2010
Messages
24
Bump.

Does anyone have a thought on how I can concatenate based on a particular variable (ie page number?)

Thanks again for your help
 

Watch MrExcel Video

Forum statistics

Threads
1,122,819
Messages
5,598,289
Members
414,223
Latest member
Accountant2B

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top