Dividing sentence in to words Formula

[raju.mothe]

Hi,


I want formula for dividing sentence in to words& characters it to columns with using excel formula.

For this text to columns works well but every time i have to run that for my work. with out running that every a formula required. Once i will enter any sentence it should be converts to words as per my requirements. Is it possible in excel. If it is please tell me how to proceed.

 

[shrivallabha]

Hi Raju,


Welcome to Chandoo.org forums.


Please elaborate your requirement a little more. Explain your setup. Or better still post a sample workbook.

http://chandoo.org/forums/topic/posting-a-sample-workbook


That said this thread comes close to your requirement:

http://chandoo.org/forums/topic/udf-to-pickextract-a-word-by-position-in-a-cell

 

[Debraj]

Hi Raju,


Shri.. already provided you a best answer.. still for testing purpose.. You can check below formula also..


http://chandoo.org/forums/topic/help-breaking-data-within-a-cell-into-separate-components#post-43907


Just copy the formula from there.

Replace "." with " "

Now paste the formula in excel using Ctrl + Shift + Enter..

=MID(" "&$A1&" ",SMALL(IF(ISERROR(SEARCH(MID(" "&$A1&" ",ROW($1:$99),1)," ")),"",ROW($1:$99)+1),COLUMN(A1)),SMALL(IF(ISERROR(SEARCH(MID(" "&$A1&" ",ROW($1:$99),1)," ")),"",ROW($1:$99)),COLUMN(A1)+1)-SMALL(IF(ISERROR(SEARCH(MID(" "&$A1&" ",ROW($1:$99),1)," ")),"",ROW($1:$99)),COLUMN(A1))-1)

Drag Bottom / Right as much required..


Regards,

Deb

 

[SirJB7]

Hi, raju.mothe!


If you can afford to use a helper column maybe you could try this normal formula solution (non-array):


A1: original cell

B1: 0

B2: =HALLAR(" ";A$1&" ";B1+1) -----> in english: =SEARCH(" ",A$1&" ",B1+1)

C2: =EXTRAE(A$1;B1+1;B2-B1-1) -----> in english: =MID(A$1,B1+1,B2-B1-1)


Copy down B2:C2 as required.


Regards!