[Solved] Want to split data, multiple rows

Discuss the spreadsheet application
Post Reply
drp_61
Posts: 64
Joined: Fri Feb 25, 2011 9:05 am

[Solved] Want to split data, multiple rows

Post by drp_61 »

 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
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Want to split data

Post by robleyd »

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
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Want to split data, multiple rows

Post by MrProgrammer »

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, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
drp_61
Posts: 64
Joined: Fri Feb 25, 2011 9:05 am

Re: Want to split data, multiple rows

Post by drp_61 »

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
Post Reply