[Solved] Want to split data, multiple rows

Discuss the spreadsheet application

[Solved] Want to split data, multiple rows

Postby drp_61 » Sun Nov 22, 2020 10:18 am

 Edit: Split from Want to split data since this is a new question about multiple rows. 
Thanks for the quick and prompt reply. This does makes this data splits in columns. Is there a possibility to make them in a single row instead of columns ? Even new helper column in same sheet or in a new sheet should be OK. Sincerely wait for reply. Original file contains repeated rows of data, i have just kept one row.
Last edited by robleyd on Mon Nov 23, 2020 10:48 am, edited 2 times in total.
Reason: Tagged [Solved]
Libreoffice 6.4.7 windows 10 enterprise, 64 bit
drp_61
 
Posts: 64
Joined: Fri Feb 25, 2011 9:05 am

Re: Want to split data

Postby robleyd » Sun Nov 22, 2020 10:56 am

You can use Edit | Copy, then Edit | Paste Special and select Paste all and Transpose to paste as columns instead of rows.

There is also a TRANSPOSE function, which may be useful. See the HELP - F1 and search for transpose for more information.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.4.6.2 (SlackBuild for 6.4.6 by Eric Hameleers) - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3560
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Want to split data, multiple rows

Postby MrProgrammer » Sun Nov 22, 2020 6:48 pm

drp_61 wrote:Is there a possibility to make them in a single row instead of columns?
The solution provided in your first post does put the values in a single row, so I am confused by your question. I will guess that you want them in a single column.

drp_61 wrote:Original file contains repeated rows of data, i have just kept one row.
So, you received a solution to your first question, however your example was not representative of the real data. I will guess that your data is something like below and you want this data in 20 rows of 1 column, that is vertical:
• ------------ Column A ------------
1 "R941","F224","B674","G853","Q133"
2 "F418","C372","O929","M425","H314"
3 "D786","Q229","E436","U796","S577"
4 "J390","S156","D232","W174","G410"

Select all the data, Edit → Copy, File → New → Text document, Edit → Paste Special → Unformatted text
Edit → Find & Replace → More options → Regular Expressions → Search for , (a single comma), Replace with \n, → Replace All
Select all the data, Edit → Copy, File → New → Spreadsheet, Edit → Paste Special → Unformatted text → Separated by → Tab → OK

[Tutorial] Text to Columns

Another approach is to use this tool to rearrange data from columns to rows:
Rearrange rectangular data values

I hope the information provided here will help you solve the problem. Please attach representative data if you need further assistance. Be sure to make your goal clear.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3904
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Want to split data, multiple rows

Postby drp_61 » Mon Nov 23, 2020 10:30 am

Thanks for the valuable feedback. As per steps described, I managed to solve it. Thanks to both Mr. programmer and Mr. Robleyd.
Libreoffice 6.4.7 windows 10 enterprise, 64 bit
drp_61
 
Posts: 64
Joined: Fri Feb 25, 2011 9:05 am


Return to Calc

Who is online

Users browsing this forum: FJCC, Google [Bot] and 26 guests