Hi!
I need to split two or more values that are contained in one cell into different raws for a new table.
thanks in advance for any help!
Hi!
I need to split two or more values that are contained in one cell into different raws for a new table.
thanks in advance for any help!
Hi,
Have you tried the "text to columns" option under the "Data" menu?
Select delimited text and use the comma as a delimiter,
Rule 1: Never merge cells
Rule 2: See rule 1
"Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".
Please try at B46 and copy down for maximum 5 values in one cell
Formula:Please Login or Register to view this content.
or textjoin for Excel 365
=TRIM(MID(SUBSTITUTE(Textjoin(", ",,$C$36:$C$42),", ",REPT(" ",100)),(ROWS(B$46:B46)-1)*100+1,100))
Last edited by Bo_Ry; 11-22-2018 at 11:28 AM. Reason: corrected formula
Hi, thanks for answering!
Yes, I have tried that but it doesn't work bucause the values I want to split are brought from another table through a formula. When I try to use this option, it selects the formula and not the data contained in the cell.
Then you have a couple of options:
You could copy and paste special > values and then use text to columns,
or use Bo_Ry's formula (@Bo-Ry - nice work!)
Bo_Ry.
Thank you for the answer. But it doesn't work for my file. Can you please explein me why you used this "$A$1:$E$1"
Last edited by jeffreybrown; 11-24-2018 at 10:29 PM.
I split text in 1 cell to 5 columns from {1,50,100,150,200} so I need array of 5 column($A$1:$E$1) = {1,2,3,4,5} to get the same dimension.
Oh! Excel 2007 doesn't have aggregate
Try this with Ctrl+Shift+Enter
Formula:Please Login or Register to view this content.
If one does not have the benefit of Excel 365 (this is no improvement on Bo's Excel 365 approach) and one is prepared to accept a helper column then the above formula can be significantly simplified and the limitation of no more than 5 comma separated elements can be removed. This approach works in (at least) all Excel versions 2017 and more recent.
With reference to the attached spreadsheet the helper range is H36:H42. Enter the following (non-array) formula into H36 and fill down to H42:
Formula:Please Login or Register to view this content.
This formula concatenates the contents of the "Change" range C36:C42 into the final cell of the helper range H42 with each element from C36:C42 separated with a comma. The "substitute" eliminates instances of double commas originating from blank lines in the "Change" range C36:C42.
Now enter the following (non-array) formula into B46 and fill down as far as you wish.
Formula:Please Login or Register to view this content.
This formula takes the comma separated list in H42 and places each element on its own row in the B column which is what was required in the original problem description.
Last edited by GeoffW283; 11-24-2018 at 10:02 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks