Varying lengths suggest you cannot use the 'fixed width' option of Text to
Columns. But how about the delimited version, with the space character as
the delimiter?

"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


--
norika
------------------------------------------------------------------------
norika's Profile: http://www.excelforum.com/member.php...fo&userid=4878
View this thread: http://www.excelforum.com/showthread...hreadid=496595