Hi Norika,
This can be a solution (in cell U15):
=IF(LEN(U13)<40,U13,LEFT(U13,FindRev(U13," ")))
where FindRev is an UDF as follows:
Public Function FindRev(StrtoSearch As String, StrSearchedFor As String) As
Long
FindRev = 0
On Error Resume Next
FindRev = InStrRev(StrtoSearch, StrSearchedFor)
End Function
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