software

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--
william_mailer
------------------------------------------------------------------------
william_mailer's Profile: www.excelforum.com/member.php...oamp;userid=31318
View this thread: www.excelforum.com/showthread...hreadid=510058This gives the left side of the text =LEFT(A1,MIN(30,IF(ISERROR(FIND(quot;
quot;,A1)),30,FIND(quot; quot;,A1))))
This gives the right =RIGHT(A1,MIN(30,LEN(A1)-IF(ISERROR(FIND(quot;
quot;,A1)),30,FIND(quot; quot;,A1))))
Have done a bit of testing but 'caveat emptor'
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

quot;william_mailerquot;
lt;william_mailer.22x0p0_1139409935.9838@excelforu m-nospam.comgt; wrote in
message news:william_mailer.22x0p0_1139409935.9838@excelfo rum-nospam.com...
gt;
gt; I have a string of text in a cell. I want to split the cell based on
gt; anything over 30 characters. But I want to also split on a space. So
gt; basically the first space before I reach 30 characters limit. Hope I
gt; explained well !! Any help would be great !
gt;
gt; Thanks
gt;
gt;
gt; --
gt; william_mailer
gt; ------------------------------------------------------------------------
gt; william_mailer's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31318
gt; View this thread: www.excelforum.com/showthread...hreadid=510058
gt;
correction for right
=RIGHT(A1,LEN(A1)-IF(ISERROR(FIND(quot; quot;,A1)),30,FIND(quot; quot;,A1)))

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

quot;william_mailerquot;
lt;william_mailer.22x0p0_1139409935.9838@excelforu m-nospam.comgt; wrote in
message news:william_mailer.22x0p0_1139409935.9838@excelfo rum-nospam.com...
gt;
gt; I have a string of text in a cell. I want to split the cell based on
gt; anything over 30 characters. But I want to also split on a space. So
gt; basically the first space before I reach 30 characters limit. Hope I
gt; explained well !! Any help would be great !
gt;
gt; Thanks
gt;
gt;
gt; --
gt; william_mailer
gt; ------------------------------------------------------------------------
gt; william_mailer's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31318
gt; View this thread: www.excelforum.com/showthread...hreadid=510058
gt;

If I understood correctly you want to split a cell if the number of
characters exceeds 30. And when you split that cell you want to split
it at the first space characted within the cell. When you say split I
am assuming you want all of the characters to the left of the space in
one cell and all of the characters to the right of the space in
another. I also assumed you did not want to inlclude the space in
either cell. If the cell containing over 30 characters is in cell A1
and the split out cell are B1 and C1.

The formula for B1 would be:

=IF(LEN(A1)gt;30,LEFT(A1,FIND(quot; quot;,A1)-1),quot;quot;)

This would capture all of the characters to the left of the space.

The formula for C1 would be:

=IF(LEN(A1)gt;30,RIGHT(A1,LEN(A1)-FIND(quot; quot;,A1)),quot;quot;)

This would capture all of the characters to the right of the space.

Hopefully I understood correctly, let me know if this helps.

Thanks,
Ray--
RJL0323
------------------------------------------------------------------------
RJL0323's Profile: www.excelforum.com/member.php...oamp;userid=19456
View this thread: www.excelforum.com/showthread...hreadid=510058
My explanation was crap, i do apologise !!

If the string is longer than 30 characters, I need the text before the
last space before the 30 character limit.........

Example:

String: This is a massively long string of text and I want to split it

Result1: This is a massively long
Result2: string of text and I want to split it

Note: If I split at 30 characters I break a word so I want to split at
the previous space !

Hope this makes sense !--
william_mailer
------------------------------------------------------------------------
william_mailer's Profile: www.excelforum.com/member.php...oamp;userid=31318
View this thread: www.excelforum.com/showthread...hreadid=510058
william_mailer Wrote:
gt; My explanation was crap, i do apologise !!
gt;
gt; If the string is longer than 30 characters, I need the text before the
gt; last space before the 30 character limit.........
gt;
gt; Example:
gt;
gt; String: This is a massively long string of text and I want to split it
gt;
gt; Result1: This is a massively long
gt; Result2: string of text and I want to split it
gt;
gt; Note: If I split at 30 characters I break a word so I want to split at
gt; the previous space !
gt;
gt; Hope this makes sense !

The only way I can figure out how to do this is with a UDF
Try this for the 'left' side:

gt; Function split_at_space(string_to_split As String, split_at As Integer)
gt; 'string_to_split is your string
gt; 'split_at is the max number characters you want in your string
gt;
gt; Dim strvar As Variant 'variant used to hold the parsed string array
gt; Dim concatstr As String 'variable used to reassemble the parsed string
gt; Dim loopcount1, loopcount2 As Integer 'counters
gt;
gt; 'first check to see if your string has more than 30 characters
gt; 'if it does not, set the formulas result to the original string
gt; If Len(string_to_split) lt; 30 Then
gt; split_at_space = string_to_split
gt; GoTo exit_function
gt; End If
gt;
gt; 'parse the string using the space as a delimiter
gt; strvar = Split(string_to_split, quot; quot;)
gt; 'check ubound to determine if the array has only one element
gt; 'if it does, then there are no spaces in your string and the formula
gt; will return your original string
gt; If UBound(strvar) = 0 Then
gt; split_at_space = string_to_split
gt; GoTo exit_function
gt; End If
gt;
gt; 'set the first counter to 0 and the concatstr variable to the first
gt; element in the array
gt; loopcount = 0
gt; concatstr = strvar(0)
gt;
gt; 'execute a loop which concatenates each element of the array until
gt; 'the new strings length is greater than split_at
gt; Do Until Len(concatstr) gt;= split_at
gt; loopcount = loopcount 1
gt; concatstr = concatstr amp; quot; quot; amp; strvar(loopcount)
gt; Loop
gt;
gt; 'execute a loop which concatenates each element of the one less time
gt; than the prior loop to drop the last element
gt; loopcount2 = 0
gt; concatstr = strvar(0)
gt; Do Until loopcount2 = loopcount - 1
gt; loopcount2 = loopcount2 1
gt; concatstr = concatstr amp; quot; quot; amp; strvar(loopcount2)
gt; Loop
gt;
gt; split_at_space = concatstr
gt; exit_function:
gt; End Function

This is designed to allow the user to determine exactly where they want
the string to split.

BTW, this is pretty quick and dirty coding.--
FrankB
------------------------------------------------------------------------
FrankB's Profile: www.excelforum.com/member.php...oamp;userid=18952
View this thread: www.excelforum.com/showthread...hreadid=510058On Wed, 8 Feb 2006 08:42:55 -0600, william_mailer
lt;william_mailer.22x0p0_1139409935.9838@excelforu m-nospam.comgt; wrote:

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

You can do this easily with quot;regular expressionsquot;.

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
xcell05.free.fr

With your string in A1, use this formula:

=REGEX.MID($A$1,quot;.{1,29}(\s|$)quot;,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