Page 1 of 1

[Solved] Want to split data, multiple rows

PostPosted: Sun Nov 22, 2020 10:18 am
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.

Re: Want to split data

PostPosted: Sun Nov 22, 2020 10:56 am
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.

Re: Want to split data, multiple rows

PostPosted: Sun Nov 22, 2020 6:48 pm
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

Re: Want to split data, multiple rows

PostPosted: Mon Nov 23, 2020 10:30 am
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.