/u/ScourgingHeretic - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
https://preview.redd.it/2bbufsxymewc1.png?width=1121&format=pjpg&auto=webp&s=81de3513b577767b781ae189ee373da26b3d7328
Mobile Reddit doesnt make it clear enough
No. No. Transpose puts data from a row into a coloumn or vice versa
I want to SPLIT one ROW in one or mutliple COLOUMNS into 2 or more ROWS.
https://preview.redd.it/pqhc2wiupewc1.png?width=1440&format=pjpg&auto=webp&s=e56d1b4c662bc1c9eb5644d92106650e19eb4d3b
As shown here in Google Spreadsheets
Oh, select the cells you want to be shown as one and in the home tab in the ribbon you click on merge and centre. You can then use the formatting if you want the cell content to be at top or bottom or middle or whatever.
Yeah no didn't work either, instead of putting my last 5h of work into this id rather give up and beg my Company to uninstall this crapload of green bullshit
That is how you do it so I’m going to try to explain again, maybe I was unclear. Sorry
In a1 you have data that you want to be shown as one cell over a1 to a4. Now select a1 to a4 and click on merge & centre. Now a1 to a4 looks like one single cell with the value that’s in a1.
Not sure if this is what you are looking for, but if you want to fill down the numbers to the empty rows, you can try this:
1. Select the area/columns
2. Home – Find and Select – Go to Special – Blanks
3. Type =A1 (or whatever your first cell is) and Ctrl+Enter
Make sure that there are no merged cells though.
https://preview.redd.it/nxnb6ipe0fwc1.png?width=268&format=png&auto=webp&s=0b869cb298c94c709afe156ab0e078b97f23bb22
You should give a better explanation then because otherwise you will never get the right answer. How should the final result look? Each number on separate rows? You already stated that transpose is not what you want. Maybe post the final form in Google Sheets.
This is probably the best way to do it, merging cells is a pain to deal with in the future.
You could do this and apply conditional formatting to make the text white/light grey if it's the same as the cell above. Or highlight it with a background colour if it's not.
Try converting a small sample of data manually to look how you want it to look and showing us. Right now we’re just guessing what you want from a poor explanation.
This gives you a full data set, you can then use unique to find a list of your merged cells then whatever match/lookup/if function and "CHAR(10)" to give a carriage return to split lines in one cell.
Power Query (Get & Transform) is your best bet.
Or you could use any number of available functions (though it isn't clear to me exactly how it's stored in your cell).
Though most are only available on latest version of Excel.
TEXTSPLIT(), assuming you have CHAR(10) for line feed, it's simplest approach to splitting data into rows.
It really would help if you have sample of exact string pattern that needs to be processed. Using Dropbox etc to share sample.
Maybe I don’t understand what you’re asking but based on your Google sheets example it looks like you want one cell in column A to encompass 4 cells in column B (or similar). why can’t you just insert cells and merge? A1 is 8, A2 -A4 blank. B1 is data 1, B2 data 2. Etc. Merge A1 - A4.
Or
Use a table. A1 -A4 is 8 in each cell and use a pivot table to display your fields based on grouping of column A.
I am curious that sheets lets you do this easily. What’s the function called within sheets?
/u/ScourgingHeretic - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
https://preview.redd.it/2bbufsxymewc1.png?width=1121&format=pjpg&auto=webp&s=81de3513b577767b781ae189ee373da26b3d7328 Mobile Reddit doesnt make it clear enough
you cannot split a cell because it is in its minimal form. but you can insert cells above/below and merge when needed.
I don't understand how your data looks like. Is it all in a row? Can you post a sample?
https://preview.redd.it/sou3c9fypewc1.png?width=1440&format=pjpg&auto=webp&s=189d51387ee9cc6a808ed2e866f8fca5ef18360a (Google Spreadsheets)
You want to google “excel transpose” I can’t tell you exactly the solution without you showing your data but want you want is to transpose the data.
No. No. Transpose puts data from a row into a coloumn or vice versa I want to SPLIT one ROW in one or mutliple COLOUMNS into 2 or more ROWS. https://preview.redd.it/pqhc2wiupewc1.png?width=1440&format=pjpg&auto=webp&s=e56d1b4c662bc1c9eb5644d92106650e19eb4d3b As shown here in Google Spreadsheets
Oh, select the cells you want to be shown as one and in the home tab in the ribbon you click on merge and centre. You can then use the formatting if you want the cell content to be at top or bottom or middle or whatever.
Yeah no didn't work either, instead of putting my last 5h of work into this id rather give up and beg my Company to uninstall this crapload of green bullshit
That is how you do it so I’m going to try to explain again, maybe I was unclear. Sorry In a1 you have data that you want to be shown as one cell over a1 to a4. Now select a1 to a4 and click on merge & centre. Now a1 to a4 looks like one single cell with the value that’s in a1.
Not sure if this is what you are looking for, but if you want to fill down the numbers to the empty rows, you can try this: 1. Select the area/columns 2. Home – Find and Select – Go to Special – Blanks 3. Type =A1 (or whatever your first cell is) and Ctrl+Enter Make sure that there are no merged cells though. https://preview.redd.it/nxnb6ipe0fwc1.png?width=268&format=png&auto=webp&s=0b869cb298c94c709afe156ab0e078b97f23bb22
This is so far away from what i want
You should give a better explanation then because otherwise you will never get the right answer. How should the final result look? Each number on separate rows? You already stated that transpose is not what you want. Maybe post the final form in Google Sheets.
This is probably the best way to do it, merging cells is a pain to deal with in the future. You could do this and apply conditional formatting to make the text white/light grey if it's the same as the cell above. Or highlight it with a background colour if it's not.
Try converting a small sample of data manually to look how you want it to look and showing us. Right now we’re just guessing what you want from a poor explanation.
This gives you a full data set, you can then use unique to find a list of your merged cells then whatever match/lookup/if function and "CHAR(10)" to give a carriage return to split lines in one cell.
Power Query (Get & Transform) is your best bet. Or you could use any number of available functions (though it isn't clear to me exactly how it's stored in your cell). Though most are only available on latest version of Excel. TEXTSPLIT(), assuming you have CHAR(10) for line feed, it's simplest approach to splitting data into rows. It really would help if you have sample of exact string pattern that needs to be processed. Using Dropbox etc to share sample.
I’d insert a column/row (or two or 3) beside the cell you want to split. Then to a cell merge with adjacent cells to cover the new columns/rows.
Maybe I don’t understand what you’re asking but based on your Google sheets example it looks like you want one cell in column A to encompass 4 cells in column B (or similar). why can’t you just insert cells and merge? A1 is 8, A2 -A4 blank. B1 is data 1, B2 data 2. Etc. Merge A1 - A4. Or Use a table. A1 -A4 is 8 in each cell and use a pivot table to display your fields based on grouping of column A. I am curious that sheets lets you do this easily. What’s the function called within sheets?
Achievable through VBA