Convert rows into 1 cell or 1 string

Y

Yagnesh

If I have a worksheet with 10 rows going down, how can I join all of
them into 1 cell.

Ex)
123 - aaa;
354 - ghg;
246 - hbf;
234 - ggd;

I want the above 4 to fit into one cell like 123 - aaa; 354 - ghg; 246
- hbf; 234 - ggd;

Can someone offer any options. I am also open to any programming
language I could do this in where convert a row into one cell or one
continuous string.
 
Y

Yagnesh

I tried the Concatenate function but it only limits 30 cells I coul
merge. I have a row of about 1,000
 
S

Steve Mackay

This is a round about way to do it...

Copy the 1000 cells and paste into Microsoft Word
From the Table menu, select Convert...Table to Text
Click to expand...
From Edit menu, select Replace..
Click to expand...
click on the More button
With the cursor in "Find what" section, click on the "Special" button,
then select Paragraph Mark
With the cursor in "Replace with" section, type a space (or a space and
semicolon, if that's what you want in between the entries)
Click on Replace All

Copy the result and paste into a cell in Excel
 
M

Morrigan

Assume A1:A1000 is your data:

B1000 = CONCATENATE(A1000,B1001) (Copy up)

Then B1 should be what you are looking for.


Hope this helps.
 
Top