The Google Sheet Assignment - Splitting and Transposing Cell based on Delimiter - Part 1

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:

{abc,defgh,ijkl20asdklj;asdlkejd,asdjkw,adkjw}

This array is provided to the CONCATENATE function that again converts it in to a single array:

{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.

Can you create acronyms from a list in Excel Sheet?

Can you crate the acronyms in Excel? If you are given a set of Strings and be asked to create the acronyms how will you proceed. My today's post is related to creating acronyms!!

Lets consider a list of string like one given here  (and adopted from here):

The formula we need to built will work by identifying the the characters followed by spaces, we will add a space to the start of each string. If the strings are present in Column A, the new string will be:

=" "&String

... and the table will look like:

You can see the extra space at the start of the string in Column D, now we can use this extra space as a delimiter and convert text-to-column operation, the operation results in words being placed in the individual columns:

Now we can get the first character of each individual word in the cell by using the formula =PROPER(LEFT(Cell_Ref)). We can simply put it in a cell below the table and drag to right and down to get another table. Here it is...

We can use now, the built in concatenate or any udf available from the internet. If we just keep it an ampersand sign, we can do it like this to get the desired result.

=TRIM(E18&F18&G18&H18&I18&J18&K18)

Obvisouly if it is a repetitive task, you can create a udf of your own or a macro to finish the task. This was just a quick tutorial to show how to do it. If you like it do share and comment.