[Solved] Entire Column string values to single string!

Discuss the spreadsheet application
Post Reply
User avatar
devpp
Posts: 26
Joined: Thu Dec 13, 2012 11:22 am

[Solved] Entire Column string values to single string!

Post by devpp »

Hi,
I have a column say column A.There nearly 150 rows of string values.i.e cells A1 to A150 has string values.I want all these values as a single line text seperated by commas :?: .
I can use =CONCATENATE(A1;",";A2;",";A3;",";A5) and so on...But i need to automate the process.Since the no of rows varies and also the length of the formula will be very long.
Thank you,
Last edited by devpp on Wed Jan 09, 2013 2:37 pm, edited 1 time in total.
OpenOffice 3.4 on Windows XP
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Entire Column string values to single string!

Post by JohnSUN-Pensioner »

I can not remember when and to whom wrote this function. But it's probably not important.

PS. Oh, sorry! The last error in the program is always only the penultimate mistake.
My apologies to those who downloaded the previous version of the file. It does not properly handle an array of conditions.
Attachments
Entire Column string values to single string v2.ods
Bug fixed
(20.64 KiB) Downloaded 386 times
Last edited by JohnSUN-Pensioner on Wed Jan 09, 2013 11:25 am, edited 1 time in total.
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Entire Column string values to single string!

Post by eremmel »

What about rolling up the concatenation from bottum up:
Assume the concatenation is in column B2:Bn and data is in column A2:An you can use:
=IF(ISBLANK(A2);"";IF(ISBLANK(A3);A2;CONCATENATE(A2;",";B3)))
The ISBLANK() tests are for the bottom of the list to prevent not needed commas.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
User avatar
devpp
Posts: 26
Joined: Thu Dec 13, 2012 11:22 am

Re: Entire Column string values to single string!

Post by devpp »

JohnSUN-Pensioner wrote:I can not remember when and to whom wrote this function. But it's probably not important.

PS. Oh, sorry! The last error in the program is always only the penultimate mistake.
My apologies to those who downloaded the previous version of the file. It does not properly handle an array of conditions.
Thank you Mr JohnSUN-Pensioner!
But the program u have attached is not working and showing #value error even when used as array formula.
I have attached a sample file with the requirement.can u do something on that!
Thank you.
Attachments
book1.ods
(7.87 KiB) Downloaded 202 times
OpenOffice 3.4 on Windows XP
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Entire Column string values to single string!

Post by JohnSUN-Pensioner »

Do you enable macro?
Attachments
book1_1.ods
(11.51 KiB) Downloaded 277 times
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
devpp
Posts: 26
Joined: Thu Dec 13, 2012 11:22 am

Re: Entire Column string values to single string!

Post by devpp »

JohnSUN-Pensioner wrote:Do you enable macro?
Thank you JohnSUN-Pensioner!
The problem was with the macro security.
Thanks a ton...
OpenOffice 3.4 on Windows XP
Post Reply