william_mailer Wrote:
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 !

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

Function split_at_space(string_to_split As String, split_at As Integer)
'string_to_split is your string
'split_at is the max number characters you want in your string

Dim strvar As Variant 'variant used to hold the parsed string array
Dim concatstr As String 'variable used to reassemble the parsed string
Dim loopcount1, loopcount2 As Integer 'counters

'first check to see if your string has more than 30 characters
'if it does not, set the formulas result to the original string
If Len(string_to_split) < 30 Then
split_at_space = string_to_split
GoTo exit_function
End If

'parse the string using the space as a delimiter
strvar = Split(string_to_split, " ")
'check ubound to determine if the array has only one element
'if it does, then there are no spaces in your string and the formula
will return your original string
If UBound(strvar) = 0 Then
split_at_space = string_to_split
GoTo exit_function
End If

'set the first counter to 0 and the concatstr variable to the first
element in the array
loopcount = 0
concatstr = strvar(0)

'execute a loop which concatenates each element of the array until
'the new strings length is greater than split_at
Do Until Len(concatstr) = split_at
loopcount = loopcount + 1
concatstr = concatstr & " " & strvar(loopcount)
Loop

'execute a loop which concatenates each element of the one less time
than the prior loop to drop the last element
loopcount2 = 0
concatstr = strvar(0)
Do Until loopcount2 = loopcount - 1
loopcount2 = loopcount2 + 1
concatstr = concatstr & " " & strvar(loopcount2)
Loop

split_at_space = concatstr
exit_function:
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: http://www.excelforum.com/member.php...o&userid=18952
View this thread: http://www.excelforum.com/showthread...hreadid=510058