Today, we are going to discuss about splitting a sentence into multiple cells in excel.
There are lot to learn, so let's dive into the topic without any delay.
Please see below pic., to understand our requirement and result.
In this method we are going to discuss about bunch of text functions and one standard construction which is widely used by excel users during text parsing.
Below is the formula used in D4 cell,
=TRIM(MID(SUBSTITUTE($B4," ",REPT(" ",LEN($B4))),((COLUMNS($D$4:D4)-1)*LEN($B4))+1,LEN($B4)))
Let me de-construct this into small pieces to make it easy for you to digest.
First, I am going to discuss about the SUBSTITUTE function which is wrapped in text argument of MID function,
So let's see what substitute does and what are the arguments?
=SUBSTITUTE(text,old_text,new_text,instance_num)
As the name suggests, it substitute the portion of text with new text.
In our case, text lies at B4 cell and so we fed that in text argument, For old text, we fed " "(space) which is the delimiter. In New text argument, Rept(Repeat) function used to repeat
" "(Space) to the length of B4 cell which is 15.
We constructed a new text using substitute function to feed in MID function as text argument, select the text part in MID function to select the entire substitute portion and press F9 to evaluate.
We replaced the existing single space with multiple spaces with length of the B4 cell value.
Newly constructed text will look like below,
Now, we will move onto the Start_num argument in MID function.,
The value what we are to going to feed here should start from 1 and increment by the length of B4 cell as we drag the formula. So we use COLUMNS function to create expandable range, both column and row locked in the first reference of $D$4, so this is absolute reference, that means it will not change as we drag the formula across columns.
2nd reference is not locked in both row and column, so it is relative reference.
Output of columns function in D4 is 1. If we multiply the length of B4 cell(15) with 1 then, we cannot start from 1 as per our requirement, so before doing multiplication if we detect 1 the result will become 0. Zero multiplied by any number is zero and then we add 1, so the output of start_num argument in D4 cell is 1.
In the E4 cell, Columns function will deliver 2, if we detect 1 it will become 1 and if we multiply it by length of B4 cell(15), it becomes 15 and finally we add 1, which gives 16 and that's where our 2nd word starts.
For third argument num_char, we use the length function to extract the length of B4 cell(15).
If we evaluate the entire MID function portion, the result will look like below pic.
Spaces before and after the text, to clean or to trim out the spaces we fed the whole text into TRIM function. Then, if we drag the formula across columns, this will place 2nd word in 2nd column and so on.
Comments