Public Function SplitString(rng As Range)
Dim s As String
s = Left(rng.Value, 30)
If Len(s) < 30 Then
SplitString = s
Else
iloc = InStrRev(s, " ", -1, vbTextCompare)
If iloc < 0 Then
S1 = Left(s, iloc - 1)
s2 = Mid(s, iloc + 1, 255)
SplitString = S1
Else
SplitString = s
End If
End If

End Function


--
Regards,
Tom Ogilvy

"william_mailer"
<william_mailer.22x6xy_1139418004.0799@excelforu m-nospam.com wrote in
message news:william_mailer.22x6xy_1139418004.0799@excelfo rum-nospam.com...

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:
http://www.excelforum.com/member.php...o&userid=31318
View this thread: http://www.excelforum.com/showthread...hreadid=510057