On Thu, 29 Dec 2005 02:47:19 -0600, norika
wrote:


I want to separate the following words into two rows. As the length of
each word is different, so i could not use 'text to coumns' function.
for example:
Word1 : the board of directors of abc company limited (len = 45) in
cell u13
Word2 : the board of directors of abcdefgh company limited (len =50) in
cell u14

I use the formula to separate word 1 into two rows: return #VALUE
1st row : =IF(LEN(U13)<40,U13,LEFT(U13,FIND(" ",U13,40))) in cell u15
2nd row : =IF(LEN(U15)<40," ",TRIM(MID(U13,LEN(U15),99))) in cell u16

But i use same formula to separate word2 into two rows : successful
1st row : =IF(LEN(U14)<40,U15,LEFT(U14,FIND(" ",U14,40))) in cell u17
2nd row : ==IF(LEN(U14)<40," ",TRIM(MID(U14,LEN(U17),99))) in cell u18

Answer : 1st row : the board of directors of abcdefgh company
2nd row : limited
what is the problem?

TIA

norika

If I understand you correctly, you are trying to place in the first row,
everything except the last word of the sentence; and in the second row just the
last word.

The problem is you have to find the last <space in the sentence in order to do
this.

The following formulas will do this:

1st row: (in R15)

=LEFT(U13,-1+FIND(CHAR(1),SUBSTITUTE(U13," ",
CHAR(1),LEN(U13)-LEN(SUBSTITUTE(U13," ","")))))

2nd row (if 1st row is not in R15, change that reference in the formula below):

=TRIM(SUBSTITUTE(U13,R15,""))



--ron