Convert Multiple Rows into One Row

[Who I Am]

One record spreads over multiple rows. The purpose is to make them one
row and save as text file (in order to import into another program).
Because of column number limitation, I use copy-paste special -
transpose.

Now each column, not multiple rows, hold a record (for one customer).

What should I do to get the records saved as CSV file?

Does MS access help here? Is there a better approach?

Thank you, my boss is demanding an answer.

 

[mr_ben]

concatenate would work in this instance


ie

=concatenate(a1,b1,c1 etc etc)


that way all would be in one cell, you can always add a commar too if
need be.

Then copy and paste the values and then save as a CSV file..

 

[Who I Am]

Thanks Ben for your prompt response. This is a very interesting
approach.

However, concatenate only handles upto 30 text items. In my case, the
maximum cells could be 490.

Is there another approach?

Does it work if I use several concatenate"

What is the maxmum number of character a cell can hold?

Thank you all.

 

[mr_ben]

you can daisy chain formulas however you can only nest 7 formulas in one
big formula


As for cell length there is something about more than 1024 characters
(IIRC) being a problem.

unfortunately my VBA skills are sadly lacking so there could be a
better way...

 

[Who I Am]

Do you think if it will work:

I use "=CONCATENATE" in one cell and allow it to hold 25 cells. In next
cell, I put second "=CONCATENATE" to hold another 25 cells....

So with 20 "=CONCATENATE"s, I can cover all my data, say, from A1:T1.

Then the question will be

Can I save it as CVS file where the entire first row is one record?

 

[mr_ben]

Do you think if it will work:

I use "=CONCATENATE" in one cell and allow it to hold 25 cells. In
next
cell, I put second "=CONCATENATE" to hold another 25 cells....

So with 20 "=CONCATENATE"s, I can cover all my data, say, from A1:T1.

Then the question will be

Can I save it as CVS file where the entire first row is one record?

Yes and..

I would image so if you have commas in the text then for every comma
the text will be put in another cell.

 

[Tom Ogilvy]

I would image so if you have commas in the text then for every comma

the text will be put in another cell.

that would be incorrect based on what it sounds like is being proposed. .

if a1 and b1 contain

a1: a,b,c
b1: d,e,f

and you did Files=>Save As and selected CSV, it would be saved as

"a,b,c","d,e,f"

If you brought it back into excel, it would be placed in A1 and B1.


---------------------------------
if you want to change rows to columns, select the single column multiple row
area (assume starting in A1), do edit=>copy

select b1 and do Edit=>Paste Special and select transpose.

Now delete column 1

then File =>Save As, and select CSV for the type.