String Extraction...

Hi I just stumbled across this web site as I am struggling with creating
a function to solve my problem....the problem is...

I want to separate a string into 3 different cells. Each cell should
not have more than 16 characters. For example:

String Example:
Used to have fun, but now I have to work.

If I separate this string using the standard LEFT and MID functions I
get: where / dictates a new cell.

Used to have fu/n, but now I hav/e to work.

I'd like to get this string to look like:

Used to have/fun, but now I/ have to work.

Can anyone help? I'd really appreciate it....




--
iceberg27
------------------------------------------------------------------------
iceberg27's Profile: http://www.hightechtalks.com/m86
View this thread: http://www.hightechtalks.com/t2261719

This doesn't exactly parse the way you asked, but I'm sure that you have to
make this work on more then just the single example you posted.

Try this and play with the number 30 in C1 to see if they might cover your
other possibilities.

String in A1.

In B1:
=LEFT(A1,FIND(" ",A1,13))

In C1:
=MID(A1,FIND(" ",A1,13)+1,FIND(" ",A1,30)-LEN(B1))

In D1:
=RIGHT(A1,LEN(A1)-(LEN(B1)+LEN(C1)))

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"iceberg27" <iceberg27.1wx7y0@no-mx.forums.yourdomain.com.au> wrote in
message news:iceberg27.1wx7y0@no-mx.forums.yourdomain.com.au...
>
> Hi I just stumbled across this web site as I am struggling with creating
> a function to solve my problem....the problem is...
>
> I want to separate a string into 3 different cells. Each cell should
> not have more than 16 characters. For example:
>
> String Example:
> Used to have fun, but now I have to work.
>
> If I separate this string using the standard LEFT and MID functions I
> get: where / dictates a new cell.
>
> Used to have fu/n, but now I hav/e to work.
>
> I'd like to get this string to look like:
>
> Used to have/fun, but now I/ have to work.
>
> Can anyone help? I'd really appreciate it....
>
>
>
>
> --
> iceberg27
> ------------------------------------------------------------------------
> iceberg27's Profile: http://www.hightechtalks.com/m86
> View this thread: http://www.hightechtalks.com/t2261719
>

thanks a tonne....


--
iceberg27
------------------------------------------------------------------------
iceberg27's Profile: http://www.hightechtalks.com/m86
View this thread: http://www.hightechtalks.com/t2261719