On Wed, 8 Feb 2006 08:42:55 -0600, william_mailer
<william_mailer.22x0p0_1139409935.9838@excelforu m-nospam.com wrote:


I have a string of text in a cell. I want to split the cell based on
anything over 30 characters. But I want to also split on a space. So
basically the first space before I reach 30 characters limit. Hope I
explained well !! Any help would be great !

Thanks

You can do this easily with "regular expressions".

If the length of the original string will be less than 256 characters, then
download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

With your string in A1, use this formula:

=REGEX.MID($A$1,".{1,29}(\s|$)",ROWS($1:1))

Copy/drag down as far as needed.

If you are breaking the string into columns, change the last argument to
COLUMNS($A:A) and copy/drag across.

That last argument is merely a counter that tells which slice of max characters
= 30 ending with a space.

===============================

If your string length might be greater than 255 characters, we can do this
using VBA Regular Expressions, which can handle more than 255 characters.


--ron