I recently had opportunity to workout a formula with google sheets, where i realized how powerful google sheet formulas are.
The problem consisted of following data:
.... To be converted this way:
The requirement was to identify the spaces in the in each cell in column B as a delimiter and separate the text based on that space, the corresponding value in the Column C was to be repeated the same number of time as was the space in the cell to left.
For example in Cell B2, the text contains two spaces, we will one more so that three words in the cell be repeated into three different cells.
As a first part, we will try to separate the text down the column:
The First Step is to Split the Values in Cells in Column B, for all cell in this column. For a single cell, the SPLIT will do the trick, for all the cells in the column, we will select the entire range of cells.
=TRANSPOSE(SPLIT(CONCATENATE(ARRAYFORMULA(SPLIT(INDIRECT("Sheet1!B$2:B"&COUNTA(Sheet1!B$1:B))," ")&" "))," "))
Using the Indirect function, we have made the selection dynamic, the resulting array is split cell wise into individual words.
With reference to our data, the COUNTA() function will return a value of 2 That will give result in range of B2:B2 provided to SPLIT() function that will return the following array:
This array is provided to the CONCATENATE function that again converts it in to a single array:
The problem consisted of following data:
.... To be converted this way:
The requirement was to identify the spaces in the in each cell in column B as a delimiter and separate the text based on that space, the corresponding value in the Column C was to be repeated the same number of time as was the space in the cell to left.
For example in Cell B2, the text contains two spaces, we will one more so that three words in the cell be repeated into three different cells.
As a first part, we will try to separate the text down the column:
The First Step is to Split the Values in Cells in Column B, for all cell in this column. For a single cell, the SPLIT will do the trick, for all the cells in the column, we will select the entire range of cells.
=TRANSPOSE(SPLIT(CONCATENATE(ARRAYFORMULA(SPLIT(INDIRECT("Sheet1!B$2:B"&COUNTA(Sheet1!B$1:B))," ")&" "))," "))
Using the Indirect function, we have made the selection dynamic, the resulting array is split cell wise into individual words.
With reference to our data, the COUNTA() function will return a value of 2 That will give result in range of B2:B2 provided to SPLIT() function that will return the following array:
{abc,defgh,ijkl20asdklj;asdlkejd,asdjkw,adkjw}
{abc defgh jkl20asdklj asdlkejd asdjkw adkjw}
This is again split and then transposed to give the desired result.
So the basic resason for joining the two texts is to make sure that we don't have to find where the next part of the array from the second has to start, once we have concatenated everything, we can just split it based on a delimiter and then transpose to get the solution!
This split and concatenate function of google is enough to made any one mad who has worked with MS Excel's concatenate function that required fixed cells to be feed to it, however this short coming is overcomes in later versions of excel, but still, google sheet is far more ahead in terms of functionality that G sheet provides.
The ArrayFormula makes sure that we don't need to copy paste it over and over again! again a feature missing in MS Excel.
