On Tue, 3 Jan 2006 01:41:02 -0800, "Stefi"
wrote:

Hi Ron,

I think the problem is that Norika not always wants to find the last space!
E.g.
l is the 40th
character
the board of directors of abc company limited
is to be split like
the board of directors of abc company
limited

but n is the 40th character
the board of directors of abcdefghijklmno company limited
like
the board of directors of
abcdefghijklmno company limited


Regards,
Stefi


OK, I read some of her other notes than the first and understand that what she
really wants to do is limit each line to no more than forty characters, still
breaking at <space. However, that is contrary to her initial example, which
actually had more than 40 characters in a line.

However, this can still be easily done with worksheet functions:

Assumption is that we will require no more than two lines:

R13:

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

R14:

=TRIM(REPLACE(U13,1,LEN(R13),""))


--ron