Hi Norika,
The problem is that there are no spaces in string "the board of directors of
abc company limited" after its 40th character (41th-45th characters being
"mited") that's why FIND(" ",U13,40) returns #VALUE.
I'm still thinking on an appropriate solution!
Regards,
Stefi
€˛norika€¯ ezt Ć*rta:
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