Lesson#100: Making a master formula to separate words from the sentence

Hello. Now we will learn how to make a master formula to separate words from sentences. For that, I will use functions, TRIM, MID, SUBSTITUTE, REPT, and LEN.

1st step: I will change the text by replacing all the <SPACE> with multiplying the original text length.

=SUBSTITUTE($A3,” “,REPT(” “,LEN($A3)))

So the words become easy to separate.

See In the A2 cell we have a name of 14 characters but it is replaced every <SPACE> with 14 nos. of <SPACE> now.

2nd step: I will extract the nth word and that is the most vital step.

=MID(SUBSTITUTE($A2,” “,REPT(” “,LEN($A2))),(C$1-1)*LEN($A2)+1,LEN($A2))

Now from that modified text, we will extract nth words. I used starting position (C$1-1)*LEN($A2)+1

So that whenever we ask for the 1st word it starts with the 1st char, whenever we ask for the 3rd word it starts from the 3*(original text length)+1 position. That works the same for nth.

As the number of characters in MID function, I used the LEN($A2) which means at least the (original text length) nos will be extracted and it will be surrounded by many <SPACE>

So now here the function is working but we are getting some extra <SPACE>

3rd step: To remove those extra <SPACE> I used the TRIM function. So the final formula becomes like this

=TRIM(MID(SUBSTITUTE($A2,” “,REPT(” “,LEN($A2))),(C$1-1)*LEN($A2)+1,LEN($A2)))

See the formula is perfectly working. Now that is a master formula to separate words from sentences.

Download the sheet